Home » SQL & PL/SQL » SQL & PL/SQL » Pivot in Oracle
Pivot in Oracle [message #251438] Fri, 13 July 2007 10:56 Go to next message
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 #251456 is a reply to message #251438] Fri, 13 July 2007 12:11 Go to previous messageGo to next message
Volder
Messages: 38
Registered: April 2007
Location: Russia
Member
Duplicate thread Laughing
Re: Pivot in Oracle [message #251460 is a reply to message #251456] Fri, 13 July 2007 12:21 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
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

Previous Topic: Database Links
Next Topic: Attributes
Goto Forum:
  


Current Time: Wed Dec 07 16:13:21 CST 2016

Total time taken to generate the page: 0.13229 seconds