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 < deptno =" 30"