Rows of Data to be Displayed as Columns

[uparna]

Hi ,
Here is one of my famous requirements again .

[COLOR=blue]Table : Flowers [/color]
[b]
FL_ID  NAME          PRICE[/b]
1      Chrysanthemum   1.09
2      Catstail        1.32
3      RedRoses        6.12

[COLOR=blue]Table : Orders [/color]
[b]
O_ID     FL_ID[/b]
101     1
101     3
201     2 
301     3
401     1
401     2
401     3

And here is Supposed to be the output that is supposed to be shown

[COLOR=blue]
[b]
OrderNumber Chrysanthemum Catstail  RedRoses [/color][/b]
101             X                      X
201                           X
301                                    X
401             X             X        X

i know i can achieve this by using a series of decodes in a subquery which is summed up in the outer query

like

SELECT O_ID, MAX(Chrysanthemum), Max(Catstail), Max(RedRoses) 
FROM
(Select O_id , 
Decode(Fl_ID , 1,'X',2,NULL,3,NULL) Chrysanthemum,
Decode(Fl_ID , 1,NULL,2,'X',3,NULL) CatsTail,
Decode(Fl_ID , 1,NULL,2,NULL,3,'X') RedRoses
FROM ORDERS
)
GROUP BY O_ID

I am just wondering if there is a more efficent way of doing this view. here are some more details : The Flowers table(master table) has around 60 selections. There are more than 20000 orders and i am sure the performance of this is going to be b.a.d .
I am not very happy with the second option of a denormalised table being created for this report ( the table would be populated by an SP which will use cursors to route through all orders and put an X in each one of the 60 columns where the order has chosen that flower)...... any other ideas , folks?


Regards,
S. Jayaram Uparna .

 

[SantaMufasa]

Jayaram,

Actually, IMHO, you have thought it out well. The Stored Procedure is an excellent solution as solutions go for such a project...It's the solution I would recommend. And we have proven in past threads that the SP solution is actually more efficient much of the time than a contrived SQL-only solution. Go for it !

Mufasa
(aka Dave of Sandy, Utah, USA @ 15:56 (30Jun04) UTC (aka "GMT" and "Zulu"), 08:56 (30Jun04) Mountain Time)

 

[uparna]

Hi Santa,
Thanks for the post. I am just wary of going in for a table which has 60 columns which will have nothing but X in them. Sad. Is this the only approach available (aprt from the view)?

Regards,
S. Jayaram Uparna .

 

[nagornyi]

Jayaram,
in your original post, what do you mean by output? If this is report, then of course you don't need either temp table or stored produre, if not report... then what?

 

[uparna]

Hi Nagornyi,
The output is read by an application (Javabased). The app simply wants to read from a view (select * from vw_whatever)

Now this view can either be the one with the decodes or it can be based on the denormalised table.

Regards,
S. Jayaram Uparna .

 

[SantaMufasa]

Jayaram,

You asked, "Is this the only approach available (aprt from the view)?". There are probably dozens of approaches with differing benefits depending upon what you would like to do with the results. The PL/SQL approach is excellent for either populating a table or for printing formatted output to either a flat-file or to the screen.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:13 (30Jun04) UTC (aka "GMT" and "Zulu"), 12:13 (30Jun04) Mountain Time)

 

[uparna]

Hi Santa ,
Thanks.
It is precisely this is what i intend to look into. For too long i have been considering the view and the Sp as the only 2 approaches available. I know that Oracle is not so limited a tool as to give me only 2 paths to solve any problem....so i want to learn abt some more so thati can open my mind....if this knowledge will be useless this time , it will cetainly prove invaluable in the next time whenever i have to do this.
Since i am still a fledgling DBA ( doubling as a programmer) , i would like to learn...... thanks!

Regards,
S. Jayaram Uparna .