Combining multiple rows into one row

Question

I’ve used the following SQL query to pull out the following data:

Select
Employee_Department, Employee_ID, DateTime_From, Wage_From, DateTime_To, Wage_To
From
wage_tbl
Where
Employee_ID = 254 or
Employee_ID = 103 or
Employee_ID = 256

The following is the result of the query:

Employee_Department	Employee_ID	DateTime_From	Wage_From	DateTime_To	Wage_To
Sales	254	11-Jun-2022 05:20:00	10	11-Jun-2022 07:20:00	15
Sales	254	11-Jun-2022 07:20:00	15	11-Jun-2022 08:20:00	30
Sales	254	11-Jun-2022 08:20:00	30	11-Jun-2022 09:20:00	15
Marketing	103	11-Jun-2022 11:20:00	30	11-Jun-2022 12:20:00	60
Marketing	103	11-Jun-2022 12:20:00	60	11-Jun-2022 14:20:00	100
Sales	256	11-Jun-2022 10:20:00	20	11-Jun-2022 11:20:00	30
Sales	256	11-Jun-2022 11:20:00	30	11-Jun-2022 13:20:00	35

I want to combine rows based on the Employee_ID column such that the table now becomes something like:

Employee_Department	Employee_ID	DateTime_1	Wage_1	DateTime_2	Wage_2	DateTime_3	Wage_3	DateTime_4	Wage_4
Sales	254	11-Jun-2022 05:20:00	10	11-Jun-2022 07:20:00	15	11-Jun-2022 08:20:00	30	11-Jun-2022 09:20:00	15
Marketing	103	11-Jun-2022 11:20:00	30	11-Jun-2022 12:20:00	60	11-Jun-2022 14:20:00	100	null	null
Sales	256	11-Jun-2022 10:20:00	20	11-Jun-2022 11:20:00	30	11-Jun-2022 13:20:00	35	null	null

I have tried using Pivot but can't seem to figure out how to get the configuration that I want.

Can someone please help with this?

Answer 1

As far as my understanding goes, this is not what a pivot table does. The closes solution I was able to come to was to aggregate the rows into a single entry with a GROUP BY (using Postgres):

select employee_department,employee_id,string_agg(CAST(datetime_from as text),', ') as datetime_from,string_agg(CAST(wage_from as text),', ') as wage_from,string_agg(CAST(datetime_to as text),', ') as datetime_to,string_agg(CAST(wage_to as text),', ') as wage_to from wage_tbl group by employee_department,employee_id;

 employee_department | employee_id |                         datetime_from                         | wage_from  |                          datetime_to                          |  wage_to
---------------------+-------------+---------------------------------------------------------------+------------+---------------------------------------------------------------+------------
 Marketing           |         103 | 2022-06-11 11:20:00, 2022-06-11 12:20:00                      | 30, 60     | 2022-06-11 00:12:20, 2022-06-11 00:14:20                      | 60, 100
 Sales               |         254 | 2022-06-11 08:20:00, 2022-06-11 05:20:00, 2022-06-11 07:20:00 | 30, 10, 15 | 2022-06-11 00:09:20, 2022-06-11 00:07:20, 2022-06-11 00:08:20 | 15, 15, 30
 Sales               |         256 | 2022-06-11 11:20:00, 2022-06-11 10:20:00                      | 30, 20     | 2022-06-11 00:13:20, 2022-06-11 00:11:20                      | 35, 30
(3 rows)

You could then split these comma seperated values into extra columns using the split_part function, but you won't be able to replicate your example, mainly because you pull the value for your DateTime1-4 fields from multiple columns (Datetime_From & Datetime_To).