Need help on PIVOT table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Need help on PIVOT table

Threaded View

  1. #1
    Join Date
    Apr 2001
    Posts
    125

    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
    Last edited by gandolf989; 04-22-2014 at 11:21 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width