In data processing, a pivot table is a data summarization tool found in data visualization programs such as spreadsheets or business intelligence software.
Among other functions, a pivot table can automatically sort, count total or give the average of the data stored in one table or spreadsheet, displaying the results in a second table showing the summarized data. Pivot tables are also useful for quickly creating unweighted cross tabulations. The user sets up and changes the summary's structure by dragging and dropping fields graphically. This "rotation" or pivoting of the summary table gives the concept its name.
What is Pivot Table in Oracle ?
The Oracle PIVOT clause allows you to write a cross-tabulation query starting in Oracle 11g. This means that you can aggregate your results and rotate rows into columns.
Syntax:
SELECT * FROM
(
SELECT column1, column2
FROM tables
WHERE conditions
)
PIVOT
(
aggregate_function(column2)
FOR column2
IN ( expr1, expr2, ... expr_n) | subquery
)
ORDER BY expression [ ASC | DESC ];
---
Example:
Old versions Prior to 11g
SELECT job,
SUM(DECODE(deptno, 10, sal, 0)) AS deptno10,
SUM(DECODE(deptno, 20, sal, 0)) AS deptno20,
SUM(DECODE(deptno, 30, sal, 0)) AS deptno30,
SUM(DECODE(deptno, 30, sal, 0)) AS deptno30
FROM emp
WHERE deptno IN (10, 20, 30, 40)
GROUP BY job
JOB deptno10 deptno20 deptno30 deptno30
Others 501
Clerk 1300 1900 950
Pivot Table Equilvalent:
WITH pivot_data AS (
SELECT deptno, job, sal
FROM emp
)
SELECT *
FROM pivot_data
PIVOT (
SUM(sal) --<-- pivot_clause
FOR deptno --<-- pivot_for_clause
IN (10,20,30,40) --<-- pivot_in_clause
);
OUTPUT
JOB 10 20 30 40
Others 501
Clerk 1300 1900 950
WITH pivot_data AS (
SELECT deptno, job, sal
FROM emp
)SELECT *
FROM pivot_data
PIVOT (SUM(sal) AS sum
, COUNT(sal) AS cnt
FOR (deptno,job) IN ((30, 'SALESMAN') AS d30_sls,
(30, 'CLERK') AS d30_clk));
D30_SLS_SUM D30_SLS_CNT T D30_CLK_SUM D30_CLK_CNT
----------- ----------- ----------- ----------- ----------- -----------
6160 4 1045 1
