DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Need help on PIVOT table

  1. #1
    Join Date
    Apr 2001
    Posts
    127

    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.

  2. #2
    Join Date
    Apr 2001
    Posts
    127

    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:20 AM.

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492
    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.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492
    It's no necessary to post a duplicate.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

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