Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

SQL Query: Switching Rows to Columns dynamically.

4 views
Skip to first unread message

Anil G

unread,
Oct 16, 2006, 6:45:11 PM10/16/06
to
Guys:

I am looking to develop SQL query which will switch the rows to
Columns. I was trying with DECODE statements but that leads to
hardcoding of types. Any one has any clues to develop dynamic logic to
convert rows to columns. Following is detailed information about
problem.

Thanks in advance,
Anil G

----------------------------------------- problem statement
---------------------------------------------------------------
type_tbl
+ name

data_tbl
+ org
+ type_name
+ data_value (FK to type_tbl)

-----
Data layouts: type_tbl, user can keep adding types from the
application.

type_tbl
--------
T-1
T-2
T-3
T-4
..
..

data_tbl
-------
org type_name data_value
---- --------- -----------
O-1 T-1 100
O-1 T-2 30
O-1 T-3 45
O-2 T-1 100
O-2 T-4 30
O-3 T-1 45


Desired Output:
---------------------------------------------------

org T-1 T-2 T-3 T-4 .....
--- --- --- --- --- --
O-1 100 30 45
O-2 100 0 0 25
O-3 45

--------------------------------------------------------- Ends
-------------------------------------------------------------

Charles Hooper

unread,
Oct 16, 2006, 8:16:03 PM10/16/06
to

I can't think of an easy way, maybe an analytical function of some
sort.

Let's look at an example from my database to see if there is an easy
way to obtain the desired output. I have a table named LABOR_TICKET
that contains the columns RESOURCE_ID, SHIFT_DATE, and HOURS_WORKED.
Assume that I want to set up a cross tab to show the number of hours
each of the RESOURCE_IDs that were in use in a given date range, but I
only want to include those RESOURCE_IDs that were in use.

First, it would be helpful if I had a list of RESOURCE_IDs that were in
use in the date range:
SELECT DISTINCT
RESOURCE_ID
FROM
LABOR_TICKET
WHERE
SHIFT_DATE>=TRUNC(SYSDATE-30)
ORDER BY
RESOURCE_ID;

Now that I have the list, I need to transform the rows into columns. I
will guess that I have a maximum of 20 RESOURCE_IDs (the same approach
can be used for more):
SELECT
MAX(DECODE(ROWNUM,1,RESOURCE_ID,NULL)) P1,
MAX(DECODE(ROWNUM,2,RESOURCE_ID,NULL)) P2,
MAX(DECODE(ROWNUM,3,RESOURCE_ID,NULL)) P3,
MAX(DECODE(ROWNUM,4,RESOURCE_ID,NULL)) P4,
MAX(DECODE(ROWNUM,5,RESOURCE_ID,NULL)) P5,
MAX(DECODE(ROWNUM,6,RESOURCE_ID,NULL)) P6,
MAX(DECODE(ROWNUM,7,RESOURCE_ID,NULL)) P7,
MAX(DECODE(ROWNUM,8,RESOURCE_ID,NULL)) P8,
MAX(DECODE(ROWNUM,9,RESOURCE_ID,NULL)) P9,
MAX(DECODE(ROWNUM,10,RESOURCE_ID,NULL)) P10,
MAX(DECODE(ROWNUM,11,RESOURCE_ID,NULL)) P11,
MAX(DECODE(ROWNUM,12,RESOURCE_ID,NULL)) P12,
MAX(DECODE(ROWNUM,13,RESOURCE_ID,NULL)) P13,
MAX(DECODE(ROWNUM,14,RESOURCE_ID,NULL)) P14,
MAX(DECODE(ROWNUM,15,RESOURCE_ID,NULL)) P15,
MAX(DECODE(ROWNUM,16,RESOURCE_ID,NULL)) P16,
MAX(DECODE(ROWNUM,17,RESOURCE_ID,NULL)) P17,
MAX(DECODE(ROWNUM,18,RESOURCE_ID,NULL)) P18,
MAX(DECODE(ROWNUM,19,RESOURCE_ID,NULL)) P19,
MAX(DECODE(ROWNUM,20,RESOURCE_ID,NULL)) P20
FROM
(SELECT DISTINCT
RESOURCE_ID
FROM
LABOR_TICKET
WHERE
SHIFT_DATE>=TRUNC(SYSDATE-30)
ORDER BY
RESOURCE_ID)

Now, I have a single row that lists all of the possible values of the
RESOURCE_IDs in the date range specified. I can then create a
cartesian join with the original table to see how the hours map into
each of the possible values of the RESOURCE_ID, and then GROUP BY the
date:
SELECT
LT.SHIFT_DATE,
SUM(DECODE(LT.RESOURCE_ID,COLS.P1,HOURS_WORKED,NULL)) P1,
SUM(DECODE(LT.RESOURCE_ID,COLS.P2,HOURS_WORKED,NULL)) P2,
SUM(DECODE(LT.RESOURCE_ID,COLS.P3,HOURS_WORKED,NULL)) P3,
SUM(DECODE(LT.RESOURCE_ID,COLS.P4,HOURS_WORKED,NULL)) P4,
SUM(DECODE(LT.RESOURCE_ID,COLS.P5,HOURS_WORKED,NULL)) P5,
SUM(DECODE(LT.RESOURCE_ID,COLS.P6,HOURS_WORKED,NULL)) P6,
SUM(DECODE(LT.RESOURCE_ID,COLS.P7,HOURS_WORKED,NULL)) P7,
SUM(DECODE(LT.RESOURCE_ID,COLS.P8,HOURS_WORKED,NULL)) P8,
SUM(DECODE(LT.RESOURCE_ID,COLS.P9,HOURS_WORKED,NULL)) P9,
SUM(DECODE(LT.RESOURCE_ID,COLS.P10,HOURS_WORKED,NULL)) P10,
SUM(DECODE(LT.RESOURCE_ID,COLS.P11,HOURS_WORKED,NULL)) P11,
SUM(DECODE(LT.RESOURCE_ID,COLS.P12,HOURS_WORKED,NULL)) P12,
SUM(DECODE(LT.RESOURCE_ID,COLS.P13,HOURS_WORKED,NULL)) P13,
SUM(DECODE(LT.RESOURCE_ID,COLS.P14,HOURS_WORKED,NULL)) P14,
SUM(DECODE(LT.RESOURCE_ID,COLS.P15,HOURS_WORKED,NULL)) P15,
SUM(DECODE(LT.RESOURCE_ID,COLS.P16,HOURS_WORKED,NULL)) P16,
SUM(DECODE(LT.RESOURCE_ID,COLS.P17,HOURS_WORKED,NULL)) P17,
SUM(DECODE(LT.RESOURCE_ID,COLS.P18,HOURS_WORKED,NULL)) P18,
SUM(DECODE(LT.RESOURCE_ID,COLS.P19,HOURS_WORKED,NULL)) P19,
SUM(DECODE(LT.RESOURCE_ID,COLS.P20,HOURS_WORKED,NULL)) P20
FROM
(SELECT
MAX(DECODE(ROWNUM,1,RESOURCE_ID,NULL)) P1,
MAX(DECODE(ROWNUM,2,RESOURCE_ID,NULL)) P2,
MAX(DECODE(ROWNUM,3,RESOURCE_ID,NULL)) P3,
MAX(DECODE(ROWNUM,4,RESOURCE_ID,NULL)) P4,
MAX(DECODE(ROWNUM,5,RESOURCE_ID,NULL)) P5,
MAX(DECODE(ROWNUM,6,RESOURCE_ID,NULL)) P6,
MAX(DECODE(ROWNUM,7,RESOURCE_ID,NULL)) P7,
MAX(DECODE(ROWNUM,8,RESOURCE_ID,NULL)) P8,
MAX(DECODE(ROWNUM,9,RESOURCE_ID,NULL)) P9,
MAX(DECODE(ROWNUM,10,RESOURCE_ID,NULL)) P10,
MAX(DECODE(ROWNUM,11,RESOURCE_ID,NULL)) P11,
MAX(DECODE(ROWNUM,12,RESOURCE_ID,NULL)) P12,
MAX(DECODE(ROWNUM,13,RESOURCE_ID,NULL)) P13,
MAX(DECODE(ROWNUM,14,RESOURCE_ID,NULL)) P14,
MAX(DECODE(ROWNUM,15,RESOURCE_ID,NULL)) P15,
MAX(DECODE(ROWNUM,16,RESOURCE_ID,NULL)) P16,
MAX(DECODE(ROWNUM,17,RESOURCE_ID,NULL)) P17,
MAX(DECODE(ROWNUM,18,RESOURCE_ID,NULL)) P18,
MAX(DECODE(ROWNUM,19,RESOURCE_ID,NULL)) P19,
MAX(DECODE(ROWNUM,20,RESOURCE_ID,NULL)) P20
FROM
(SELECT DISTINCT
RESOURCE_ID
FROM
LABOR_TICKET
WHERE
SHIFT_DATE>=TRUNC(SYSDATE-30)
ORDER BY
RESOURCE_ID)) COLS,
LABOR_TICKET LT
WHERE
LT.SHIFT_DATE>=TRUNC(SYSDATE-30)
GROUP BY
LT.SHIFT_DATE;

In the above, if a row's RESOURCE_ID is equal to the value in a
specific column from the COLS inline view, its HOURS_WORKED is added to
that column's total.

Just something to get you headed in the right direction. Warning: the
above is not thoroughly tested.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

ian

unread,
Oct 17, 2006, 8:45:09 AM10/17/06
to
Hi;

I know this is NOT strictly an SQL answer, but I have just done this
using Perl.

I do cheat in the SQL by ||'!'|| each of the columns into one wide "set
lines 5000" row per row set.
Then a simple perl script to "pivot" all this into a nice tall Excel
sheet for my user(s).

Perl is basically :-
While (<>){
read each line into an array
}
{
Foreach column
foreach row
print cell
}

I currently pivot over 300 columns into no more than 25 rows using
"where ... and rownum < 25"
Made my life much easier, and my butt shines!!
I even union'ed an header row so users can see each named Excel row.

Sorry if this is no help. Just my bit to the list.

Regards
Ian

Michel Cadot

unread,
Oct 17, 2006, 12:04:25 PM10/17/06
to

"Anil G" <anil....@gmail.com> a écrit dans le message de news: 1161038711.6...@i3g2000cwc.googlegroups.com...

I posted a solution for such a problem on Ask Tom (it was about students and classes but the principle is the same):
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:52266643928180#52284733269998

Regards
Michel Cadot


0 new messages