-
Need help on PIVOT table
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
-
Need help on PIVOT table
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
-
You must use the "code" tags to format your code, as it is posted it's unreadable.
Also the results you expect seem to not match the sample data you posted, where did "'pk1', 'type1', 'pk1_type1_value2')" go?
Please modify your post with a formatted example and matching expected results.
:rolleyes:
-
It's no necessary to post a duplicate.
:p