-
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
-
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');
-
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
-
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
-
Originally Posted by ebrian
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|