3 Replies Latest reply: Aug 28, 2009 3:13 PM by Andy Tael-Oracle RSS

    Transpose query

    user527060
      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
          Andy Tael-Oracle
          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
            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
              Andy Tael-Oracle
              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