Re: SQL Query: Switching Rows to Columns dynamically.

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 16 Oct 2006 17:16:03 -0700
Message-ID: <1161044163.175675.20960@b28g2000cwb.googlegroups.com>

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. Received on Mon Oct 16 2006 - 19:16:03 CDT