Question: Can you please share some examples of using the Oracle
SQL pivot operator?
Answer: The function PIVOT transposes rows in columns and the
function UNPIVOT transposes columns in rows. They have been added in 11g.
WITH
T
AS
(
SELECT
DEPTNO
FROM
EMP
)
SELECT
*
FROM
T
PIVOT
(
COUNT(*)
FOR
(DEPTNO)
IN
(10,20,30,40)
);
10 20 30
40
---------- ---------- ---------- ----------
3 5 6 0
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 3 (0)| 00:00:01 |
| 1 | VIEW | | 1 | 52 | 3 (0)| 00:00:01 |
| 2 | SORT AGGREGATE | | 1 | 3 | | |
| 3 | TABLE ACCESS FULL| EMP | 14 | 42 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Four columns are created for the departments 10, 20, 30 and 40.
For each column, the number of corresponding rows is counted.
Compare with:
SELECT
DEPTNO,
COUNT(*)
FROM
EMP
GROUP BY
DEPTNO;
DEPTNO COUNT(*)
---------- ----------
30 6
20 5
10 3
In the first statement, one row is returned with all departments. In the
second statement each department is on a different row. The columns that are not
aggregated and not pivoted will return multiple rows:
WITH
T
AS
(
SELECT
DEPTNO,
JOB,
SAL
FROM
EMP
)
SELECT
*
FROM
T
PIVOT
(
MIN(SAL) AS MINSAL,
MAX(SAL) AS MAXSAL
FOR
(JOB)
IN
(
'CLERK' AS CLERK,
'SALESMAN' AS SALES
)
)
ORDER BY
DEPTNO;
DEPTNO CLERK_MINSAL
CLERK_MAXSAL SALES_MINSAL SALES_MAXSAL
---------- ------------ ------------ ------------ ------------
10 1300 1300
20 800 1100
30 950 950 1250 1600
Three rows are selected. The job is the pivot, the salaries
are aggregated and the departments are returned as distinct rows. Note the
different values for the pivot are explicitly listed.
The inline view T contains three columns, the salary is
aggregated, the job is transposed into multiple columns, and the remaining
column is used as a group for the aggregation. The remaining column is the
department, which contains three distinct values; each value returns exactly one
row. To specify the group by the department, it is therefore necessary to select
only the department in addition to the aggregated values and to the transposed
column.
Here are some working examples of the
pivot operator from the book "SQL
Design Patterns", highly recommended. Also see how to
pivot rows to columns and pivot columns to rows.
SQL Server 2005
introduced the pivot operator as syntax extension for a table
expression in the from clause:
select *
from
(Sales pivot (sum(Amount) for Month in (?Jan?, ?Feb?, ?Mar?))
There is nothing like JanCnt
in the original data. Indeed there is not, but transforming the month column
data into the new column with the Cnt postfix can be done easily with
string concatenation. Therefore, the answer to the problem is:
select scount.*, ssum.* from (
select * from (
(select product, month || ?Cnt?, amount from Sales)
pivot (count(*) for Month in (?JanCnt?, ?FebCnt?, ?MarCnt?)
) scount, (
select * from (
(select product, month || 'sum?, amount from Sales)
pivot (sum(Amount) for Month in (?JanSum?, ?FebSum?, ?MarSum?)
) ssum
where scount.product = ssum.product
Unpivot in a standard SQL syntax
is:
select product, ?Jan? as Month, Jan as Amount from PivotedSales
union all
select product, ?Feb? as Month, Feb as Amount from PivotedSales
union all
select product, ?Mar? as Month, Mar as Amount from PivotedSales
Again it is repetitive and not dynamically extensible. In SQL Server 2005
syntax, it becomes:
select * from
(PivotedSales unpivot (Amount for Month in (?Jan?, ?Feb?, ?Mar?))
And here are pivot examples from John Palinski:
The For clause matches the aggregated
FK_department values produced by the upper query to the values in the
Subquery list. If a value does not exist in the Subquery list, it is not
displayed as a column.
select *
from
(select fk_department
from employee)
pivot
(count(fk_department)
for fk_department in ('INT', 'WEL', 'CEN', 'POL'));
'INT' 'WEL' 'CEN' 'POL'
---------- ---------- ----------
----------
7 6 0 8
select * from
(select current_position, fk_department, wages
from employee)
pivot
(sum(wages)
for fk_department in ('INT', 'WEL', 'CEN', 'POL'));
Department wages by current position view
create view pivotTable as
select *
from
(select current_position, fk_department, wages
from employee)
pivot
(sum(wages)
for fk_department in ('INT' as int, 'WEL' as wel,
'CEN' as cen, 'POL' as pol));
|
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|