Tuesday, 20 March 2007

Translate Columns into Rows (Subquery Factoring)

It has always been a fairly rudimentary task pivoting a result set so that rows are displayed as columns. More recently I had the requirement to translate a result set the other way so that the columns would be displayed as rows.

For Example

Your result set starts out like this:

SITE COST1 COST2 COST3 COST4
------------------------------------------------------------------
SITE_ONE 2000 255
SITE_TWO 100
SITE_THREE 145 5000

The desired output should look like this:

SITE VALUE
------------------------------
SITE_ONE 2000
SITE_ONE 255
SITE_TWO 100
SITE_THREE 145
SITE_THREE 5000

Thanks to the help from Mr. Tom Kyte at http://asktom.oracle.com I was able to produce the desired results using the following query:

WITH data AS
(SELECT LEVEL l
FROM dual CONNECT BY LEVEL <= 4) SELECT site, decode(l, 1, cost1, 2, cost2, 3, cost3, 4, cost4) cost FROM data, costs WHERE decode(l, 1, cost1, 2, cost2, 3, cost3, 4, cost4) IS NOT NULL ORDER BY 1

It uses something called Subquery Factoring which allows you to create a pseudo table with N rows (CONNECT BY LEVEL <= N) in your query. You then cartisian join to your other tables(s) and use a simple DECODE to extract the column you are interested in.

Note that this technique will only work in 9i and above but I have had great success in the past with it The thread on asktom can be found here

4 comments:

Kris said...

Excellent Duncan,This is exactly what I need at the moment.

Deepak- A car fanatic said...

Hi..This really helped me..Thnx a lot !!

Steve said...
This comment has been removed by the author.
Steve said...

Here is another method I created recently to turn columns into rows.