1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle Unpivoting (Column to row Conversion) Techniques (SQL)

Discussion in 'SQL PL/SQL' started by rajavu, Oct 20, 2008.

  1. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Unpivoting technique is just opposite of pivoting technique. ie Column to row Conversion. In Oracle it is done basically using the Cartesian Join .

    For example ,

    See the following table EMP_JOB_PIVOT

    Code (Text):

    SQL> SELECT * FROM EMP_JOB_PIVOT;

        DEPTNO    ANALYST      CLERK    MANAGER  PRESIDENT   SALESMAN
    ---------- ---------- ---------- ---------- ---------- ----------
            30          0        950       2850          0       1600
            20       3000       1100       2975          0          0
            10          0       1300       2450       5000          0

    SQL>
     
    by using the Unpivoting method we can display the result in row wise format as below.

    Code (Text):

    SQL> SELECT   deptno,
      2              DECODE ( l, 1, 'ANALYST' ,
      3               DECODE(l, 2, 'CLERK',
      4                DECODE(l, 3, 'MANAGER',
      5                 DECODE(l, 4, 'PRESIDENT',
      6                  DECODE(l, 5, 'SALESMAN'  ))))) JOB  ,
      7     DECODE ( l, 1, ANALYST ,
      8               DECODE(l, 2, CLERK,
      9                DECODE(l, 3, MANAGER,
     10                 DECODE(l, 4, PRESIDENT,
     11                  DECODE(l, 5, SALESMAN ))))) SAL
     12      FROM
     13      EMP_JOB_PIVOT ,
     14      (SELECT level l FROM DUAL X CONNECT BY LEVEL <=5) DMY
     15      Order by 1 ,2;

        DEPTNO JOB              SAL
    ---------- --------- ----------
            10 ANALYST            0
            10 CLERK           1300
            10 MANAGER         2450
            10 PRESIDENT       5000
            10 SALESMAN           0
            20 ANALYST         3000
            20 CLERK           1100
            20 MANAGER         2975
            20 PRESIDENT          0
            20 SALESMAN           0
            30 ANALYST            0
            30 CLERK            950
            30 MANAGER         2850
            30 PRESIDENT          0
            30 SALESMAN        1600

    15 rows selected.

    SQL>
     
    Here we produce the Cartesian product and then decode accordingly. Note that we cant do the reverse of a PIVOT operation as it cannot undo aggregations made by PIVOT.
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Similarly Oracle has introduced a new builtin feature in Oracle 11g Exclusively for this purpose known as 'UNPIVOT' . The syntax for Pivot is as follows

    Example

    Code (Text):

    SELECT * FROM EMP_JOB_PIVOT
      UNPIVOT INCLUDE NULLS (SAL FOR
        JOB IN ('ANALYST', 'CLERK', 'MANAGER', 'PRESIDENT','SALESMAN'))
        ORDER by 1, 2
     
    This will also produce the same result as above .Note that we cant do the reverse of a PIVOT operation as it cannot undo aggregations made by PIVOT.

    NB : code is not tested as I dont have acces to 11g.