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

Comments

Kris said…
Excellent Duncan,This is exactly what I need at the moment.
20 March 2007 at 13:04
Deepak- A car fanatic said…
Hi..This really helped me..Thnx a lot !!
15 May 2009 at 07:15
Steve said…
This comment has been removed by the author.
17 March 2011 at 06:57
Steve said…
Here is another method I created recently to turn columns into rows.
17 March 2011 at 06:59
Post a Comment

Popular posts from this blog

Custom Authentication / Authorisation Schemes (Part 1)

I often see posts on the APEX forum asking how to implement custom Authentication / Authorisation schemes within their applications. The following is something I have used in several apps over the last couple of years and provides a great base for securing your application. The code base is largley based on the rather excellent article " Storing Passwords in the Database " found here with a few tweaks and changes. Firstly, just to clarify, Authentication Schemes control access to the application and Authorisation Schemes control access to page items / regions and even pages themselves. Implementing your own Authentication I tend to set up an APP_USERS table that stores Username and encrypted passwords that I Authenticate against when page 101 is submitted. All this will be explained in detail as we go. Create the APP_USERS table CREATE TABLE APP_USERS ( USERNAME VARCHAR2(10), PASSWORD VARCHAR2 (255) ); Create the Application Security Package CREATE OR REPLACE PACKAGE app_sec...
Read more

Custom Error Handling in APEX

If like me you have written several PL/SQL procedures within your APEX app, you will have had to deal with the issue of error handling. As a general rule, I try to hide all Oracle Error Messages from the user and replace these with something more meaningful for example: If a user breaks a unique key by trying to insert the same value twice, the following error message is not uncommon: ORA-00001 Unique constraint violated There is little or no merit rendering this message to a user as those outside the Oracle community, may not be able to deduce what went wrong. We can capture this error and translate it to a user friendly message explaining what went wrong for example: The record you tried to create already exists. Please try again In order to achieve this in APEX, the process is relativly simple. Set up your Custom Error Page 1. Create a new page with the following attributes: Page type: Blank Page Page Number: your choice (for this example I used page 500) Page Alias: ERRPAGE Name:...
Read more

Sum to Parent Nodes in Hierarchy Queries: CONNECT_BY_ROOT

In one of our Applicaitons, the table DEPT contains a self referncing join (Pigs Ear) as it models our organsational department hierarchy. For example: CREATE TABLE DEPT ( DEPT_ID NUMBER NOT NULL ,PARENT_ID NUMBER ,DEPT_NAME VARCHAR2 (100) NOT NULL ,EMPLOYEES NUMBER NOT NULL ,CONSTRAINT DEPT_PK PRIMARY KEY (DEPT_ID) ,CONSTRAINT DEPT_FK01 FOREIGN KEY (PARENT_ID) REFERENCES DEPT (DEPT_ID) ); INSERT INTO DEPT VALUES (1,NULL,'IT', 100); INSERT INTO DEPT VALUES (2,1,'DEVELOPMENT', 12); INSERT INTO DEPT VALUES (3,1,'SUPPORT', 15); INSERT INTO DEPT VALUES (4,1,'TEST', 25); INSERT INTO DEPT VALUES (5,2,'PL/SQL', 2); INSERT INTO DEPT VALUES (6,2,'Java', 1); INSERT INTO DEPT VALUES (7,2,'SQL', 11); INSERT INTO DEPT VALUES (8,2,'C++', 3); INSERT INTO DEPT VALUES (9,4,'Functional', 3); INSERT INTO DEPT VALUES (10,4,'Non Functional', 5); COMMIT; A quick tree walk using CONNECT BY PRIOR shows you the Parent / Child rel...
Read more