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

Thread: Oracle 9i Pivot ??

  1. #1
    Join Date
    Jul 2009
    Posts
    3

    Oracle 9i Pivot ??

    Need some help with Pivot function. I have data like this

    Col1 Col2 Col3
    1 Option1 Val1
    1 Option2 Val2
    1 Option3 Val3
    1 Option4 Val4
    1 Option5 Val5
    2 Option1 Val6
    2 Option2 Val7
    2 Option4 Val8

    3 Option2 Val9
    3 Option3 Val10
    3 Option4 Val11
    3 Option5 Val12

    I need to pivot it like this, But how much ever I try, i cannot seem to getit right

    Val Option1 Option2 Option3 Option4 Option5
    1 Val1 Val2 Val3 Val4 Val5
    2 Val6 Val7 - Val8 -
    3 - Val9 Val10 Val11 Val12

    I am using Oracle 9i and would prefer to do it in SQL

  2. #2
    Join Date
    Jul 2009
    Posts
    3
    Here's the dmls:
    create table TEMP1
    (
    COL1 VARCHAR2(64),
    COL2 VARCHAR2(64),
    COL3 VARCHAR2(64)
    );
    insert into TEMP1 (COL1, COL2, COL3)
    values ('1', 'Option1', 'Val1');
    insert into TEMP1 (COL1, COL2, COL3)
    values ('1', 'Option2', 'Val2');
    insert into TEMP1 (COL1, COL2, COL3)
    values ('1', 'Option3', 'Val3');
    insert into TEMP1 (COL1, COL2, COL3)
    values ('1', 'Option4', 'Val4');
    insert into TEMP1 (COL1, COL2, COL3)
    values ('1', 'Option5', 'Val5');
    insert into TEMP1 (COL1, COL2, COL3)
    values ('2', 'Option1', 'Val6');
    insert into TEMP1 (COL1, COL2, COL3)
    values ('2', 'Option2', 'Val7');
    insert into TEMP1 (COL1, COL2, COL3)
    values ('2', 'Option4', 'Val8');
    insert into TEMP1 (COL1, COL2, COL3)
    values ('3', 'Option2', 'Val9');
    insert into TEMP1 (COL1, COL2, COL3)
    values ('3', 'Option3', 'Val10');
    insert into TEMP1 (COL1, COL2, COL3)
    values ('3', 'Option4', 'Val11');
    insert into TEMP1 (COL1, COL2, COL3)
    values ('3', 'Option5', 'Val12');

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool Sys_connect_by_path

    Try this:
    Code:
    SQL> COL col3s format a60
    SQL> SELECT     col1, SUBSTR (MAX (SYS_CONNECT_BY_PATH (col3, ',')), 2) col3s
      2        FROM (SELECT col1, col3,
      3                     ROW_NUMBER () OVER (PARTITION BY col1 ORDER BY col3) rn
      4                FROM temp1)
      5  START WITH rn = 1
      6  CONNECT BY PRIOR rn = rn - 1 AND PRIOR col1 = col1
      7    GROUP BY col1
      8  /
    
    COL1           COL3S
    -------------- ------------------------------------------------------------
    1              Val1,Val2,Val3,Val4,Val5
    2              Val6,Val7,Val8
    3              Val10,Val11,Val12,Val9
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  4. #4
    Join Date
    Apr 2006
    Posts
    377
    Thanks for the create scripts !

    Code:
    SQL> select col1 val,
      2     max(decode(col2, 'Option1', col3)) Option1,
      3     max(decode(col2, 'Option2', col3)) Option2,
      4     max(decode(col2, 'Option3', col3)) Option3,
      5     max(decode(col2, 'Option4', col3)) Option4,
      6     max(decode(col2, 'Option5', col3)) Option5
      7  from temp1
      8  group by col1
      9  order by 1;
    
    VAL        OPTION1    OPTION2    OPTION3    OPTION4    OPTION5
    ---------- ---------- ---------- ---------- ---------- ----------
    1          Val1       Val2       Val3       Val4       Val5
    2          Val6       Val7                  Val8
    3                     Val9       Val10      Val11      Val12

  5. #5
    Join Date
    Jul 2009
    Posts
    3
    Quote Originally Posted by ebrian View Post
    Thanks for the create scripts !

    Code:
    SQL> select col1 val,
      2     max(decode(col2, 'Option1', col3)) Option1,
      3     max(decode(col2, 'Option2', col3)) Option2,
      4     max(decode(col2, 'Option3', col3)) Option3,
      5     max(decode(col2, 'Option4', col3)) Option4,
      6     max(decode(col2, 'Option5', col3)) Option5
      7  from temp1
      8  group by col1
      9  order by 1;
    
    VAL        OPTION1    OPTION2    OPTION3    OPTION4    OPTION5
    ---------- ---------- ---------- ---------- ---------- ----------
    1          Val1       Val2       Val3       Val4       Val5
    2          Val6       Val7                  Val8
    3                     Val9       Val10      Val11      Val12
    Great! That was what I was looking for! I guess I was messing up somewhere using the group clause

    Hey! and to all the guys that took the time, Thanks for your help!!

Tags for this Thread

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