Use GROUP BY without Aggregation to get row data into separate columns

Question

I have a table which looks like this

Emp_ID	Name	Status	Status_Change_Date
1	John	Active	01-01-2022
2	John	Inactive	01-02-2022

I want to write a query to create an output in following format

Emp_ID	Name	Active Date	Inactive Date
1	John	01-01-2022	01-02-2022

I tried using "case" and "group by" as follows

 Select emp_id, 
        name, 
        case when status = active then status_change_date end as "Active Date", 
        case when status = Inactive then status_change_date end as "Inactive Date" 
from empdata 
group by emp_id

In this case it shows empty "Inactive Date" column, if I remove group by it just shows distinct rows for each status. Any way I can achieve this?

score 0 · Answer 1 · answered Feb 15 '22 at 08:09

Always put strings in quotes, you need to use aggregate function to achieve what you want, Emp_ID I took min because.
Try:

 Select min(emp_id), 
        name, 
        max(case when status = 'Active' then status_change_date end) as "Active Date", 
        max(case when status = 'Inactive' then status_change_date end) as "Inactive Date" 
from empdata 
group by name

Result:

min(emp_id)   name    Active Date   Inactive Date
1             John      2022-01-01    2022-02-01

Demo

Use GROUP BY without Aggregation to get row data into separate columns

1 Answers1