matrix/crosstab/pivot report in SQL

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;

                                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