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 Pivoting (Row to Column 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
    As all you know, relational tables are, well, tabular—that is, they are presented in a column-value pair. Consider the case of a table named EMP .

    Code (Text):

    SQL> desc EMP
     Name                                      Null?    Type
     ----------------------------------------- -------- --------------------

     EMPNO                                     NOT NULL NUMBER(4)
     ENAME                                              VARCHAR2(10)
     JOB                                                VARCHAR2(9)
     MGR                                                NUMBER(4)
     HIREDATE                                           DATE
     SAL                                                NUMBER(7,2)
     COMM                                               NUMBER(7,2)
     DEPTNO                                             NUMBER(2)

    SQL>
     
    Now the simple select query wi give you the output

    Code (Text):

    SQL> SELECT empno , ename , job, sal from  EMP;

         EMPNO ENAME      JOB              SAL
    ---------- ---------- --------- ----------
          7369 SMITH      CLERK            800
          7499 ALLEN      SALESMAN        1600
          7521 WARD       SALESMAN        1250
          7566 JONES      MANAGER         2975
          7654 MARTIN     SALESMAN        1250
          7698 BLAKE      MANAGER         2850
          7782 CLARK      MANAGER         2450
          7788 SCOTT      ANALYST         3000
          7839 KING       PRESIDENT       5000
          7844 TURNER     SALESMAN        1500
          7876 ADAMS      CLERK           1100
          7900 JAMES      CLERK            950
          7902 FORD       ANALYST         3000
          7934 MILLER     CLERK           1300

    14 rows selected.

    SQL>
     
    Suppose If we want to Transpose the query output to row-value instead of column-value , it is called Pivoting ( Rows to Coulmn Conversion) in SQL. The output will be transposed as

    Code (Text):

            10 CLARK      KING       MILLER
            20 SMITH      JONES      SCOTT      ADAMS      FORD
            30 ALLEN      WARD       MARTIN     BLAKE      TURNER     JAMES
     
    There are two methods used popularly for Pivoting in Oracle SQLO( From Oracle 8i) . They are

    1. Using Decode ( or CASE )
    2. Using Sys_connect_by_path

    1. Using Decode

    This method uses Decode and group by together.

    Example 1 : Displays the employee names in a row for each department.

    Code (Text):

    SQL> SELECT  deptno,
      2          MAX(DECODE ( rn , 1, ename )) EMP1,
      3          MAX(DECODE ( rn , 2, ename )) EMP2,
      4          MAX(DECODE ( rn , 3, ename )) EMP3,
      5          MAX(DECODE ( rn , 4, ename )) EMP4,
      6          MAX(DECODE ( rn , 5, ename )) EMP5,
      7          MAX(DECODE ( rn , 6, ename )) EMP6
      8  FROM
      9  (SELECT  empno , ename, deptno,
     10           row_number() OVER ( partition by deptno  order by rownum) rn
     11  FROM  EMP)
     12  GROUP BY deptno;

        DEPTNO EMP1       EMP2       EMP3       EMP4       EMP5       EMP6
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
            10 CLARK      KING       MILLER
            20 SMITH      JONES      SCOTT      ADAMS      FORD
            30 ALLEN      WARD       MARTIN     BLAKE      TURNER     JAMES

    SQL>
     
    Example 2 : Dispalys the job wise salary in a row for each Department .

    Code (Text):

    SQL> SELECT deptno,
      2         NVL(MAX(DECODE ( job , 'ANALYST', sal )),0) ANALYST,
      3         NVL(MAX(DECODE ( job , 'CLERK', sal )),0)CLERK,
      4         NVL(MAX(DECODE ( job , 'MANAGER', sal )),0) MANAGER,
      5         NVL(MAX(DECODE ( job , 'PRESIDENT', sal )),0) PRESIDENT,
      6         NVL(MAX(DECODE ( job , 'SALESMAN', sal )),0)SALESMAN
      7  FROM  EMP
      8  group by  deptno;

        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>
     
    Advantage of this method is that it gives more flexibility over the flow and coding . And disadvantage is that this methods can be used only in case of row attributeds are fixed or limitted or values are known. ie, this method is feasible only if the attributed for display are known and limitted.

    2. Using Sys_connect_by_path

    As Sys_connect_by_path is used along with hierarchical queries, we need to make any query in that format for doing the pivot . It returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECT BY condition.

    Example 1 : Displays the employee names seperated by comma in a row for each department.

    Code (Text):

    SQL> SELECT deptno ,
      2         LTRIM(MAX( SYS_CONNECT_BY_PATH ( ename, ',')),',') EMP_STRING
      3  FROM
      4  (SELECT  empno , ename, deptno,
      5           row_number() OVER ( PARTITION BY deptno  ORDER BY rownum) rn
      6  FROM  EMP )
      7  CONNECT  BY  deptno = PRIOR deptno
      8          AND  rn     = PRIOR rn+1
      9  START WITH rn =1
     10  GROUP BY deptno
     11  ORDER BY deptno;

        DEPTNO EMP_STRING
    ---------- ------------------------------------------------------------
            10 CLARK,KING,MILLER
            20 SMITH,JONES,SCOTT,ADAMS,FORD
            30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

    SQL>
     
    This method is recommended if row values are unknown or not fixed.

    Conclusion

    Pivoting techniques ( Rows to Coulmn conversion) is very possible in Oracle SQL and it can be done using DECODE or Sys_connect_by_path ( from Oracle 8i).Though Decode gives more flexibility , Sys_connect_by_path is recommended in case row values are unknown or not fixed.
     
  2. rajavu

    rajavu Forum Guru

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

    Example

    Code (Text):

    SELECT Deptno,
               Analyst,
               Clerk,
               Manager,
               President,
               Salesman
     FROM (
      SELECT deptno ,
                 job,
                 sal
      FROM EMP
      WHERE deptno > 0)
    PIVOT (SUM(sal)
    FOR job
    IN ('ANALYST' AS ANALYST ,
        'CLERK' AS CLERK,
        'MANAGER' AS MANAGER,
        'PRESIDENT' AS PRESIDENT,
        'SALESMAN' AS SALESMAN)
     );
     
    NB : code is not tested as i dont have acces to 11g.
     
  3. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    good work raj,
    my friend asked me one doubt how to interchage the colmn values to rows.
    i.e. changing the entire emp table.
    right now one record displays in row wise, the same records are to be displayed in column wise. i.e. in one column record of emp table should be displayed.

    7369 7499. .....
    smith allen .....
    ..... ..... .....
    20 30

    in this manner
    i tried and got, but the query itself too lengthy , i use max,decode,tochar, rownum etc.
    is there any short query to display it
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    If the requirement is wired, the answer could be lengthy,
    There could be better answers.. Keep on trying ...

    Don't forget to post the result if you get the shortest query , so that the Club oracle society will be benefited.
     
  5. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    K sure raj.
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    There are two more methods to be mentioned.

    1. LISTAGG : works from Oracle 11g.
    2. Wm_Concat : works from Oracle 9i

    Code (SQL):
    SQL> SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
      2  FROM   emp
      3   GROUP BY deptno;

        DEPTNO EMPLOYEES
    ---------- ----------------------------------------
            10 CLARK,KING,MILLER
            20 ADAMS,FORD,JONES,SCOTT,SMITH
            30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

    SQL> SELECT deptno, wm_concat(ename) AS employees
      2  FROM   emp
      3  GROUP BY deptno;

        DEPTNO EMPLOYEES
    ---------- ----------------------------------------
            10 CLARK,MILLER,KING
            20 SMITH,FORD,ADAMS,SCOTT,JONES
            30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD
     
     
  7. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    wm_concat is not working oracle 9i rel 2.

    any setups needed for wm_concat????
     
  8. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India

    WM_CONCAT is part of Work Manager (WMSYS schema) . It is a part of that schema since Oracle 8i.

    Please Not that , this is non-documented function for oracle. You can use it for testing purpose. But never deploy in production servers.
     
  9. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Another Solution for Row To Column Conversion is by using XML functions.
    One Example is given below.
    Code (SQL):

    SQL> SELECT deptno, ename FROM emp;

        DEPTNO ENAME
    ---------- ----------
            20 SMITH
            30 ALLEN
            30 WARD
            20 JONES
            30 MARTIN
            30 BLAKE
            10 CLARK
            10 KING
            30 TURNER
            20 ADAMS
            30 JAMES
            20 FORD

    12 ROWS selected.

    SQL> SELECT DEPTNO, RTRIM(EXTRACT(EXML,'//Empname/text()'),',') EMPLOYEES
      2  FROM (SELECT E.DEPTNO,
      3        xmlelement("EMP",
      4            XMLAGG(xmlelement("Empname",E.ENAME||',')  ORDER BY ENAME))EXML
      5        FROM EMP E
      6        GROUP BY E.DEPTNO);

        DEPTNO EMPLOYEES
    ---------- --------------------------------------------------
            10 CLARK,KING
            20 ADAMS,FORD,JONES,SMITH
            30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

    SQL>
     
     
  10. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    And I tested the PIVOT function in oracle 11g.


    Code (SQL):

    SQL> SELECT *
    FROM (SELECT DEPTNO, ENAME,
      2    3               ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY ENAME) RN
      4        FROM EMP)
      5  PIVOT (MAX(ENAME) FOR RN IN (1 AS "First",2 AS "Second",
      6                               3 AS "Third",4 AS "Fourth" ,
      7                               5 AS "Fifth", 6 AS "Sixth"));

        DEPTNO FIRST      SECOND     Third      Fourth     Fifth      Sixth
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
            10 CLARK      KING
            20 ADAMS      FORD       JONES      SMITH
            30 ALLEN      BLAKE      JAMES      MARTIN     TURNER     WARD

    SQL>
     
     
  11. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    And another way of doing it with Connect by and Sys_connect_by_path. But here , we wont be using Group by cluase ; instead CONNECT_BY_ISLEAF is used. CONNECT_BY_ISLEAF is introduced in 10g. Hence this solution work on Oracle 10g and later versions.
    Code (SQL):


    SQL> SELECT deptno ,
              LTRIM(SYS_CONNECT_BY_PATH ( ename, ','),',') EMP_STRING
      2    3     FROM
      4    (SELECT  empno , ename, deptno,
      5              ROW_NUMBER() OVER ( PARTITION BY DEPTNO  ORDER BY ROWNUM) RN
      6     FROM EMP )
      7  WHERE CONNECT_BY_ISLEAF=1
      8  CONNECT  BY  deptno = PRIOR deptno
      9  AND  RN     = PRIOR RN+1
     10  START WITH rn =1
     11  ORDER BY deptno;

        DEPTNO EMP_STRING
    ---------- --------------------------------------------------
            10 CLARK,KING
            20 SMITH,JONES,ADAMS,FORD
            30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

    SQL>
     
     
  12. nazzmca

    nazzmca Guest

    wonderful, thanks a ton, this PIVOT saves me a lot.

    Best Regards,
    Nazmul
    :)