Question: Can you please share some examples of using the Oracle
SQL pivot operator?
Answer: Here are some working examples of the
pivot operator from the book "SQL
Design Patterns", highly recommended:
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));
