Forum Stats

  • 3,854,962 Users
  • 2,264,441 Discussions
  • 7,905,847 Comments

Discussions

Oracle 9i Pivot ??

713388
713388 Member Posts: 1
edited Jul 22, 2009 5:44PM in SQL & PL/SQL
Hey GuysNGals

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

Can any one on u point me in the right direction?
Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,506 Red Diamond
    edited Jul 22, 2009 5:43PM
    Hi,

    Welcome to the forum!

    What have you tried so far, that seems closest to what you want? I don't want to explain things that you already know.
    (See my next message for a big hint.)

    It looks like you know the correct term to search for, "pivot". There are lots of examples out there for you to copy and modify.

    Edited by: Frank Kulash on Jul 22, 2009 5:42 PM
  • vijz
    vijz Member Posts: 682
    Hi,

    Pivot is available from oracle 11g. can he work on that in Oracle 9i?

    Thanks
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,506 Red Diamond
    edited Jul 22, 2009 5:44PM
    Hi,
    user10679113 wrote:
    Hi,

    Pivot is available from oracle 11g. can he work on that in Oracle 9i?
    Yes. A new, improved, clearer, easier way of pivoting was introduced in Oracle 11.
    In any version of Oracle that has CASE (or DECODE), you can use the old way, e.g.,
    SELECT    col1
    ,         MIN (CASE WHEN col2 = 'Option1' THEN col3 END)    AS option1
    ...
    GROUP BY  col1
This discussion has been closed.