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

---

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