Skip navigation.

How does one code a matrix/crosstab/pivot report in SQL?

Newbies frequently ask how one can display "rows as columns" or "columns as rows". Look at these example crosstab queries (also sometimes called transposed, matrix or pivot queries):

SELECT  *
  FROM  (SELECT job,
                sum(decode(deptno,10,sal)) DEPT10,
                sum(decode(deptno,20,sal)) DEPT20,
                sum(decode(deptno,30,sal)) DEPT30,
                sum(decode(deptno,40,sal)) DEPT40
           FROM scott.emp
       GROUP BY job)
ORDER BY 1;

JOB           DEPT10     DEPT20     DEPT30     DEPT40
--------- ---------- ---------- ---------- ----------
ANALYST                    6000
CLERK           1300       1900        950
MANAGER         2450       2975       2850
PRESIDENT       5000
SALESMAN                              5600

Here is the same query with some fancy headers and totals:

SQL> ttitle "Crosstab Report"
SQL> break on report;
SQL> compute sum of dept10 dept20 dept30 dept40 total on report;
SQL>
SQL> SELECT     *
  2    FROM     (SELECT job,
  3                  sum(decode(deptno,10,sal)) DEPT10,
  4                  sum(decode(deptno,20,sal)) DEPT20,
  5                  sum(decode(deptno,30,sal)) DEPT30,
  6                  sum(decode(deptno,40,sal)) DEPT40,
  7                  sum(sal)                   TOTAL
  8             FROM emp
  9            GROUP BY job)
 10  ORDER BY 1;

Mon Aug 23                                                             page    1
                                Crosstab Report

JOB           DEPT10     DEPT20     DEPT30     DEPT40      TOTAL
--------- ---------- ---------- ---------- ---------- ----------
ANALYST                    6000                             6000
CLERK           1300       1900        950                  4150
MANAGER         2450       2975       2850                  8275
PRESIDENT       5000                                        5000
SALESMAN                              5600                  5600
          ---------- ---------- ---------- ---------- ----------
sum             8750      10875       9400                 29025

Here's another variation on the theme:

SQL> SELECT DECODE(MOD(v.row#,3)
  2                 ,1, 'Number: '  ||deptno
  3                 ,2, 'Name: '    ||dname
  4                 ,0, 'Location: '||loc
  5                 ) AS "DATA"
  6    FROM dept,
  7         (SELECT rownum AS row# FROM user_objects WHERE rownum < 4) v
  8   WHERE deptno = 30
  9  /

DATA
--------------------------------------- ---------
Number: 30
Name: SALES
Location: CHICAGO

I would also like to suggest

I would also like to suggest the following SQL in oracle9i - using CUBE to get the totals:

SELECT job,
                NVL(sum(decode(deptno,10,sal)),0) DEPT10,
                NVL(sum(decode(deptno,20,sal)),0) DEPT20,
                NVL(sum(decode(deptno,30,sal)),0) DEPT30,
                NVL(sum(decode(deptno,40,sal)),0) DEPT40,
                SUM(SAL) TOTAL 
           FROM emp
       GROUP BY CUBE(job)
ORDER BY 1