PIVOT
From Oracle FAQ
PIVOT is a SQL operation, introduced in Oracle 11g, that lets you write cross tabulation (also called transposed, crosstab and matrix) queries that rotate rows into columns while aggregating data in the rotation process.
Example[edit]
SELECT * FROM (SELECT job, deptno, sum(sal) sal FROM emp GROUP BY job, deptno) PIVOT ( sum(sal) FOR deptno IN (10, 20, 30, 40) ); JOB 10 20 30 40 --------- ---------- ---------- ---------- ---------- CLERK 1300 1900 950 SALESMAN 5600 PRESIDENT 5000 MANAGER 2450 2975 2850 ANALYST 6000
For Oracle versions prior to 11g, the same results can be obtained with this (somewhat bulkier) query:
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;
Also see[edit]
- UNPIVOT - rotates data from columns into rows