2 Replies Latest reply: May 7, 2012 3:11 PM by patfmnd RSS

    Transposing columnar monthly data to rows

    patfmnd
      Hi,
      I need to transpose the data in the current query from this:
      (Projects with monthly allocation of Resources in hours.)

      Proj     Resrc     jan     feb     mar     apr
      proj1     abc     5     9     6     9
      proj1     def     4     7     8     0
      proj1     hij     30     0     0     0
      proj1     klm     0     40     30     20
      proj2     nop     20     30     10     20
      proj2     qrs     5     9     6     9
      proj2     abc     30     0     0     0
      proj3     klm     0     40     30     20
      proj3     nop     26     34     40     20
      proj3     tuv     20     23     50     20
      proj3     xyz     4     7     8     0
      proj4     abc     30     0     0     0


      To this:

      Proj Resrc month hours
      proj1 abc jan 5
      proj1 abc feb 9
      proj1 abc mar 6
      proj1 abc apr 9
      proj1 def jan 4
      proj1 def feb 7
      proj1 def mar 8
      proj1 def apr 0
      proj1 hij jan 30
      proj1 hij feb 0
      proj1 hij mar 0
      proj1 hij apr 0
      ....
      .....
      proj2 nop jan 20
      proj2 nop feb 30
      proj2 nop mar 10
      proj2 nop apr 20
      proj2 grs jan 5
      proj2 grs feb 9
      proj2 grs mar 6
      proj2 grs apr 9
      proj2 abc jan 30
      proj2 abc feb 0
      proj2 abc mar 0
      proj2 abc apr 0
      .....
      .....

      My data is in 11g. Wonder what would be the best way to do this in SQL or Pl/SQL? Would unpivot be able to get me there? I am not doing any grouping of data, just transposing monthly columns to rows as above.

      Thanks for any help.

      Pat
        • 1. Re: Transposing columnar monthly data to rows
          Frank Kulash
          Hi, Pat,
          patfmnd wrote:
          ... My data is in 11g. Wonder what would be the best way to do this in SQL or Pl/SQL? Would unpivot be able to get me there?
          Yes, the SELECT ... UNPIVOT feature in Oracle 11 is all you need. There are examples in the SQL Language manual:
          http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#sthref6829

          You didn't post CREATE TABLE and INSERT statements for your table, so I'll use scott.emp as an example.
          A simple, unpivoted query, showing 3 numeric columns (empno, sal and comm) might be:
          SELECT       ename, job, empno, sal, comm
          FROM       scott.emp
          WHERE       deptno     = 30
          ORDER BY  ename
          ;
          Output:
          ENAME      JOB            EMPNO        SAL       COMM
          ---------- --------- ---------- ---------- ----------
          ALLEN      SALESMAN        7499       1600        300
          BLAKE      MANAGER         7698       2850
          JAMES      CLERK           7900        950
          MARTIN     SALESMAN        7654       1250       1400
          TURNER     SALESMAN        7844       1500          0
          WARD       SALESMAN        7521       1250        500
          Now say you want to unpivot these results. Instead of having 6 rows, each with 3 number columns, 18 rows, each with only 1 of those numebrs, like this:
          ENAME      JOB       LABEL         NUM
          ---------- --------- ------ ----------
          ALLEN      SALESMAN  COMM          300
          ALLEN      SALESMAN  EMPNO        7499
          ALLEN      SALESMAN  SALARY       1600
          BLAKE      MANAGER   COMM
          BLAKE      MANAGER   EMPNO        7698
          BLAKE      MANAGER   SALARY       2850
          JAMES      CLERK     COMM
          JAMES      CLERK     EMPNO        7900
          JAMES      CLERK     SALARY        950
          MARTIN     SALESMAN  COMM         1400
          MARTIN     SALESMAN  EMPNO        7654
          MARTIN     SALESMAN  SALARY       1250
          TURNER     SALESMAN  COMM            0
          TURNER     SALESMAN  EMPNO        7844
          TURNER     SALESMAN  SALARY       1500
          WARD       SALESMAN  COMM          500
          WARD       SALESMAN  EMPNO        7521
          WARD       SALESMAN  SALARY       1250
          Here's one way to get those results:
          SELECT       ename, job, label, num
          FROM       scott.emp
          UNPIVOT       INCLUDE NULLS
                 (     num
                 FOR     label     IN ( empno
                              , sal     AS 'SALARY'     -- rename if you want to
                            , comm     AS 'COMM'
                            )
                 )
          WHERE       deptno     = 30
          ORDER BY  ename
          ,            label
          ;
          Edited by: Frank Kulash on May 2, 2012 5:43 PM
          Added example from scott.emp
          • 2. Re: Transposing columnar monthly data to rows
            patfmnd
            Frank,
            Thanks much for your reply and example. This really helped me understand how the unpivot works. I did get it to work for my data!

            Pat