Pivot in Oracle [message #251438] |
Fri, 13 July 2007 10:56  |
kalkumar
Messages: 8 Registered: July 2007
|
Junior Member |
|
|
Hi,
I have the table: App_Options like this:
APPID Options_CD
100 1
100 2
100 3
200 1
200 3
200 5
330 1
330 5
I have another table which describes the Options details:
Options_CD Desc
1 aa
2 bb
3 cc
4 dd
5 ee
6 ff
Now I need to get the result in the following way
AppID Option1 Option2 Option3 Option4 Option5 Option6
100 True True True False Flase Flase
200 True False True False True false
330 True False False False True False
If the applications if they have a value in AppOptions Table then in my result I need to show True.
How to handle this situation, we don't know how many options are there?
Thanks in advance
|
|
|
|
Re: Pivot in Oracle [message #251460 is a reply to message #251456] |
Fri, 13 July 2007 12:21  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
So we can post the solution (I don't think the OP will do it) and close the thread:
SQL> with rt as
2 (select 100 APPID, 1 Options_CD from dual union all
3 select 100, 2 from dual union all
4 select 100, 3 from dual union all
5 select 200, 1 from dual union all
6 select 200, 3 from dual union all
7 select 200, 5 from dual union all
8 select 330, 1 from dual union all
9 select 330, 5 from dual)
10 select appid,
11 max(decode(Options_CD,1,'True','False')) Option1,
12 max(decode(Options_CD,2,'True','False')) Option2,
13 max(decode(Options_CD,3,'True','False')) Option3,
14 max(decode(Options_CD,4,'True','False')) Option4,
15 max(decode(Options_CD,5,'True','False')) Option5,
16 max(decode(Options_CD,6,'True','False')) Option6
17 from rt group by appid order by appid;
APPID OPTIO OPTIO OPTIO OPTIO OPTIO OPTIO
---------- ----- ----- ----- ----- ----- -----
100 True True True False False False
200 True False True False True False
330 True False False False True False
Regards
Michel
|
|
|