Hi, I have a table I need to pivot the result:
Code:
create table tab1 (col1 varchar2(50), col2 varchar2(50), col3 varchar2(50));
insert into tab1 (col1, col2, col3) values ('pk1', 'type1', 'pk1_type1_value1');
insert into tab1 (col1, col2, col3) values ('pk1', 'type1', 'pk1_type1_value2');
insert into tab1 (col1, col2, col3) values ('pk2', 'type1', 'pk2_type1_value1');
insert into tab1 (col1, col2, col3) values ('pk2', 'type2', 'pk2_type2_value1');
insert into tab1 (col1, col2, col3) values ('pk2', 'type2', 'pk2_type2_value2');
commit;
-- I need the output like the following, comma dilimited values for type1_value and type2_value:
col1 type1 type1_value type2 type2_value
pk1 type1 pk1_type1_value1,pk1_type1_value1 null null
pk2 type1 pk2_type1_value1 type2 pk2_type2_value1,pk2_type2_value2
-- my select
select tab1.col1, case when tab1.col2 = 'type1' then 'type1' else null end as type1,
listagg((case when tab1.col2 = 'type1' then tab1.col3 else null end), ',') within group (order by tab1.col3) as type1_value,
case when tab1.col2 = 'type2' then 'type2' else null end as type2,
listagg((case when tab1.col2 = 'type2' then tab1.col3 else null end), ',') within group (order by tab1.col3) as type2_value
from tab1
group by tab1.col1, case when tab1.col2 = 'type1' then 'type1' else null end, case when tab1.col2 = 'type2' then 'type2' else null end;
-- the output shows two rows for pk2, how to combine the two rows into 1
col1 type1 type1_value type2 type2_value
pk1 type1 pk1_type1_value1,pk1_type1_value2 null null
pk2 null null type2 pk2_type2_value1,pk2_type2_value2
pk2 type1 pk2_type1_value1 null null