Anil G wrote:
> 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
> -------------------------------------------------------------
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.
