Oracle 9i Pivot ??

713388

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?

Frank Kulash

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

Hi,

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

Thanks

Frank Kulash

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