Matrix arrangement [message #20377] |
Mon, 20 May 2002 00:05 |
Pierre
Messages: 2 Registered: May 2002
|
Junior Member |
|
|
Gents,
I would be grateful if you can help me in the following issue. I have a pertial result of a ROLLUP based query
which I would like to store on a Oracle table in a different format.
Time Region Department Profit
---- ------ ---------- ------
1996 Central VideoRental 75,000
1996 Central VideoSales 74,000
1996 East VideoRental 89,000
1996 East VideoSales 115,000
1996 West VideoRental 87,000
1996 West VideoSales 86,000
Practically, I would like to arrange this result in the following matrix format:
o On columns I want to have Time, Region, VideoRental, VideoSales
o On rows I want to have Time, Region and the Profit which is associated which each Department
Despite the fact that there are other tools which can arrange this information in the format required
(like Excel, Discoverer or Oracle reports) I would like to store this information in a Oracle table.
Thanks in advance,
Pierre
|
|
|
Re: Matrix arrangement [message #20380 is a reply to message #20377] |
Mon, 20 May 2002 05:06 |
sridhar
Messages: 119 Registered: December 2001
|
Senior Member |
|
|
You could do the following,
SELECT Time, Region,
MAX(DECODE(Department, 'VideoRental', Profit, Null)) VideoRental,
MAX(DECODE(Department, 'VideoSales', Profit, Null)) VideoSales
FROM table_name
GROUP BY Time, Region;
Cheers,
SriDHAR
|
|
|