pivotting a table in SQL using case statements-CodePudding

I have the following table:

tbl

ID  service_date vax_type   dose   age   gender
A   2020-01-04   pfizer     dose1  30    M
A   2020-02-04   pfizer     dose2  30    M
B   2020-02-10   pfizer     dose1  40    M   
B   2020-03-04   pfizer     dose2  40    M
C   2020-03-14   moderna    dose1  50    F
C   2020-04-01   moderna    dose2  50    F

I would like to pivot this as such:

ID  vax_type  dose1      dose2       age   gender
A   pfizer    2020-01-04 2020-02-04  30    M
B   pfizer    2020-02-10 2020-03-04  40    M
C   moderna   2020-03-14 2020-04-01  50    F

I used case statements and tried:

select ID, vax_type, age, gender
      case when dose = 'dose1' then service_date else null end as dose1,
      case when dose = 'dose2' then service_date else null end as dose2

from tbl;

This is giving me error. A variant of this worked, but didn't give me the wanted table. Is there a better alternative for a simple pivot operation like this?

CodePudding user response：

Aggregate and then take the MAX of the CASE expressions:

SELECT ID, vax_type, age, gender,
       MAX(CASE WHEN dose = 'dose1' THEN service_date END) AS dose1,
       MAX(CASE WHEN dose = 'dose2' THEN service_date END) AS dose2
FROM yourTable
GROUP BY ID, vax_type, age, gender;

CodePudding user response：

you can try using the PIVOT function also, may be that could also work for you.

create table tbl (id varchar2,service_date date, vax_type varchar2, dose varchar2, age integer, gender varchar2(1));
select * from tbl
insert into tbl Values('A',   '2020-01-04',   'pfizer' , 'dose1',  30 , 'M');
insert into tbl Values('A',   '2020-02-04',   'pfizer',   'dose2',  30 , 'M');
insert into tbl Values('B',   '2020-02-10',   'pfizer',   'dose1' , 40, 'M');   
insert into tbl Values('B',   '2020-03-04',   'pfizer',   'dose2' , 40, 'M');
insert into tbl Values('C',   '2020-03-14',   'moderna',   'dose1',  50, 'F');
insert into tbl Values('C',   '2020-04-01',   'moderna',   'dose2' , 50 , 'F');

select *
 from tbl
 pivot(max(service_date) for dose in ('dose1', 'dose2'))
 as p(ID,  vax_type,       age,   gender,dose1, dose2);