This discussion is archived
3 Replies Latest reply: Aug 28, 2009 1:13 PM by A Tael RSS

Transpose query

user527060 Newbie
Currently Being Moderated
Hi, I am using oracle 10g.

for the following data_set, I want to transpose the rows to columns and get results like below.


WITH DATA_SET AS (
select '10/5/2009' dt , 'ABC' product , '83' leads from dual union all
select '10/5/2009' , 'XYZ' , '2412' from dual union all
select '10/5/2009' , 'PQR' , '46' from dual union all
select '10/5/2009' , 'RST' , '4' from dual union all
select '10/5/2009' , 'LMN' , '3' from dual union all
select '10/1/2009' , 'ABC' , '83' from dual union all
select '10/2/2009' , 'XYZ' , '2412' from dual union all
select '10/2/2009' , 'PQR' , '46' from dual union all
select '10/1/2009' , 'RST' , '4' from dual union all
select '10/3/2009' , 'LMN' , '3' from dual
) select * from DATA_SET

WITH RESULT_SET AS (
SELECT '10/5/2009' dt,'83' ABC,'2412' XYZ,'46' PQR,'4' RST,'3' LMN,'2548' TOTAL FROM DUAL UNION ALL
SELECT '10/1/2009','83','0','0','4','0','87' FROM DUAL UNION ALL
SELECT '10/2/2009','0','2412','46','0','0','2458' FROM DUAL UNION ALL
SELECT '10/3/2009','0','0','0','0','3','3' FROM DUAL
) select * from RESULT_SET
  • 1. Re: Transpose query
    A Tael Journeyer
    Currently Being Moderated
    If you are on version 11.x then take a look at the PIVOT function. If not, search asktom.oracle.com, I've seen solutions there for other versions of the DB.

    -Andy
  • 2. Re: Transpose query
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Below is a brief introduction to pivots.
    For the problem you described, you'll want to use MIN or MAX instead of COUNT.
    --     How to Pivot a Result Set (Display Rows as Columns)
    
    --     For Oracle 10, and earlier
    --     Actually, this works in any version of Oracle, but the 
    --     "SELECT ... PIVOT" feature introduced in Oracle 11
    --     is better.  (See Query 2, below.)
    
    --     This example uses the scott.emp table.
    --     Given a query that produces three rows for every department,
    --     how can we show the same data in a query that has one row
    --     per department, and three separate columns?
    
    --     For example, the query below counts the number of employess
    --     in each departent that have one of three given jobs:
    
    PROMPT     ==========  0. Simple COUNT ... GROUP BY  ==========
    
    SELECT     deptno
    ,     job
    ,     COUNT (*)     AS cnt
    FROM     scott.emp
    WHERE     job     IN ('ANALYST', 'CLERK', 'MANAGER')
    GROUP BY     deptno
    ,          job;
    
    /*
    Output:
    
        DEPTNO JOB              CNT
    ---------- --------- ----------
            20 CLERK              2
            20 MANAGER            1
            30 CLERK              1
            30 MANAGER            1
            10 CLERK              1
            10 MANAGER            1
            20 ANALYST            2
    */
    
    
    PROMPT     ==========  1. Pivot  ==========
    
    SELECT     deptno
    ,     COUNT (CASE WHEN job = 'ANALYST' THEN 1 END)     AS analyst_cnt
    ,     COUNT (CASE WHEN job = 'CLERK'   THEN 1 END)     AS clerk_cnt
    ,     COUNT (CASE WHEN job = 'MANAGER' THEN 1 END)     AS manager_cnt
    FROM     scott.emp
    WHERE     job     IN ('ANALYST', 'CLERK', 'MANAGER')
    GROUP BY     deptno;
    
    
    /*
    --     Output:
    
        DEPTNO ANALYST_CNT  CLERK_CNT MANAGER_CNT
    ---------- ----------- ---------- -----------
            30           0          1           1
            20           2          2           1
            10           0          1           1
    
    
    --     Explanation
    
    (1) Decide what you want the output to look like.
         (E.g. "I want a row for each department,
         and columns for deptno, analyst_cnt, clerk_cnt and manager_cnt)
    (2) Get a result set where every row identifies which row
         and which column of the output will be affected.
         In the example above, deptno identifies the row, and
         job identifies the column.
         Both deptno and job happened to be in the original table.
         That is not always the case; sometimes you have to
         compute new columns based on the original data.
    (3) Use aggregate functions and CASE (or DECODE) to produce
         the pivoted columns.  
         The CASE statement will pick
         only the rows of raw data that belong in the column.
         If each cell in the output corresponds to (at most)
         one row of input, then you can use MIN or MAX as the
         aggregate function.
         If many rows of input can be reflected in a single cell
         of output, then use SUM, COUNT, AVG, STRAGG, or some other 
         aggregate function.
         GROUP BY the column that identifies rows.
    */
    
    
    PROMPT     ==========  2. Oracle 11 PIVOT  ==========
    
    WITH     e     AS
    (     -- Begin sub-query e to SELECT columns for PIVOT
         SELECT     deptno
         ,     job
         FROM     scott.emp
    )     -- End sub-query e to SELECT columns for PIVOT
    SELECT     *
    FROM     e
    PIVOT     (     COUNT (*)
              FOR     job     IN     ( 'ANALYST'     AS analyst
                             , 'CLERK'     AS clerk
                             , 'MANAGER'     AS manager
                             )
         )
    ;
    
    /*
    NOTES ON ORACLE 11 PIVOT:
    
    (1) You must use a sub-query to select the raw columns.
    An in-line view (not shown) is an example of a sub-query.
    (2) GROUP BY is implied for all columns not in the PIVOT clause.
    (3) Column aliases are optional.  
    If "AS analyst" is omitted above, the column will be called 'ANALYST' (single-quotes included).
    */
    {code}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • 3. Re: Transpose query
    A Tael Journeyer
    Currently Being Moderated
    Doh!
    Missed that simple fact that you are on 10g. (note to myself, read before starting building query):
    WITH DATA_SET AS (
    select '10/5/2009' dt , 'ABC' product , '83' leads from dual union all
    select '10/5/2009' , 'XYZ' , '2412' from dual union all
    select '10/5/2009' , 'PQR' , '46' from dual union all
    select '10/5/2009' , 'RST' , '4' from dual union all
    select '10/5/2009' , 'LMN' , '3' from dual union all
    select '10/1/2009' , 'ABC' , '83' from dual union all
    select '10/2/2009' , 'XYZ' , '2412' from dual union all
    select '10/2/2009' , 'PQR' , '46' from dual union all
    select '10/1/2009' , 'RST' , '4' from dual union all
    select '10/3/2009' , 'LMN' , '3' from dual
    ) select * from DATA_SET
      pivot ( SUM(leads) for product in ('ABC', 'XYZ', 'PQR', 'RST', 'LMN'));
    -Andy

    Edited by: A Tael on Aug 28, 2009 3:13 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points