Pivoting is all the rage. Turning rows into columns. You can do it in Excel, in all BI/OLAP tools and we could do it in Oracle using clever tricks and highly advanced solutions, such as Anton’s:https://technology.amis.nl/blog/?p=1207 . Now in Oracle 11g, PIVOT has become a standard SQL operator. A bit like an Aggregation function.
Let’s look at an example: the first, simple PIVOT operation, show the number of employees hired in a certain year (from 1980 through 1982) – per department. The hireyears provide the columns, the departments the rows:
select *
from (select deptno
, extract (year from hiredate) hireyear
from emp
) pivot ( count(hireyear) count_emps
for hireyear in (1980, 1981, 1982)
)
/
DEPTNO 1980_COUNT_EMPS 1981_COUNT_EMPS 1982_COUNT_EMPS
---------- --------------- --------------- ---------------
30 0 6 0
20 1 2 0
10 0 2 1
3 rows selected.
What happens here is that we select all rows from emp, with for each row two columns: DEPTNO and HIREYEAR. Then we tell Oracle to pivot by HIREYEAR. That basically means that we will lose the HIREYEAR column and gain a column for every value in the HIREYEAR column that we allow – in this case 1980 through 1982. The columns created for these values are called columnvalue_PIVOTCOLUMN, for example 1980_HIREYEAR.
The rows are now condensed: we get one row for every combination of values of the non-pivot columns. Here we pivot by HIREYEAR and are left with the DEPTNO column. We get a row for every value of that DEPTNO column.
Finally we can specify which value the query should return for the new cells – the combination of a DEPTNO and a HIREYEAR. In those cells, we can define the result of an aggregation – as if GROUP BY DEPTNO, HIREYEAR. In this example I have asked for COUNT, returning the number of employees per DEPTNO/HIREYEAR combination.
It is easy to add an extra cell with more information on the combination of Department and Hireyear. What about the average salary for the employees hired in that year in that department?
SQL> select *
2 from (select deptno
3 , extract (year from hiredate) hireyear
4 , sal
5 from emp
6 ) pivot ( count(hireyear) count_emps
7 , sum(sal) sal_sum
8 for hireyear in (1980, 1981, 1982)
9 )
10 /
DEPTNO 1980_COUNT_EMPS 1980_SAL_SUM 1981_COUNT_EMPS 1981_SAL_SUM 1982_COUNT_EMPS 1982_SAL_SUM
---------- --------------- ------------ --------------- ------------ --------------- ------------
30 0 6 10103.13 0
20 1 800 2 5975 0
10 0 2 7450 1 1300
3 rows selected.
This query adds a little bit extra: it gives us column totals under our matrix report:
select *
from (select deptno
, sal
, extract (year from hiredate) hireyear
, 1 num
from emp
union all
select to_number(null) deptno
, avg(sal) avg_sal
, extract (year from hiredate) hireyear
, count(*) num
from emp
where extract (year from hiredate) in (1980, 1981, 1982)
group
by to_number(null) ,extract (year from hiredate)
) pivot ( sum(num) count_emps
, avg(sal) sal_avg
for hireyear in (1980, 1981, 1982)
)
order
by deptno
/
DEPTNO 1980_COUNT_EMPS 1980_SAL_AVG 1981_COUNT_EMPS 1981_SAL_AVG 1982_COUNT_EMPS 1982_SAL_AVG
---------- --------------- ------------ --------------- ------------ --------------- ------------
10 2 3725 1 1300
20 1 800 2 2987.5
30 6 1683.855
1 800 10 2352.813 1 1300
4 rows selected.
It seems not possible to use ROLLUP for these column totals – as the names of the pivot-created-column were not accepted in the rollup.
Reporting
The most obvious use for PIVOT seems to be in reporting query results in a way that is more appropriate for further processing or easy interpretation, given its nature. Data is frequently stored in normalized, transaction format, to allow flexible processing and for example aggregations. However, some data is interpreted by client applications and humans alike in a more denormalized way. Take for example a time registration system. Its data design includes the table WORKED_HOURS:
SQL> select *
2 from worked_hours
3 /
EMPNO DAY HOURS
---------- ---------- ----------
1210 1 8
1215 1 2
1210 2 7.5
1215 2 7.5
1210 3 8.5
1215 3 8
1210 4 4.5
1215 4 7.5
1210 5 8
1215 5 8
Fine for Oracle, less suitable for readily interpretation by us. Using PIVOT we can easily present the recorded time in a week-based overview:
select empno
, "1" as Monday
, "2" as Tuesday
, "3" as Wednesday
, "4" as Thursday
, "5" as Friday
from (select empno
, hours
, day
from worked_hours
) pivot ( sum(hours)
for day in (1,2,3,4,5)
)
/
EMPNO MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY
---------- ---------- ---------- ---------- ---------- ----------
1215 2 7.5 8 7.5 8
1210 8 7.5 8.5 4.5 8
2 rows selected.
XML Output – flexible and dynamic pivot column value processing
There is another form of PIVOT in 11g, the PIVOT XML. This operator returns the PIVOT result in XML format
for
select *
from (select deptno
, extract (year from hiredate) hireyear
, sal
from emp
) pivot xml ( count(hireyear) count_emps
, sum(sal) sal_sum
for hireyear in (any)
)
/
output
DEPTNO HIREYEAR_XML
--------------------------------------------------------------------------------------------------------------
10 <PivotSet><item><column name = "HIREYEAR">1981</column><column name = "COUNT_EMPS">2</column>
<column name = "SAL_SUM">7450</column></item>
<item><column name = "HIREYEAR">1982</column><column name = "COUNT_EMPS&qu ot;>1</column>
<column name = "SAL_SUM">1300</column></item>
</PivotSet>
20 <PivotSet><item><column name = "HIREYEAR">1980</column><column name = "COUNT_EMPS">1</column>
<column name = "SAL_SUM">800</column></item>
<item><column name = "HIREYEAR">1981</column><column name = "COUNT_EMPS">2</column>
<column name = "SAL_SUM">5975</column></item>
<item><column name = "HIREYEAR">1987</column><column name = "COUNT_EMPS">2</column>
<column name = "SAL_SUM">4850</column></item>
</PivotSet>
30
<PivotSet><item><column name = "HIREYEAR">1981</column><column name = "COUNT_EMPS">6</column><column name = "S
AL_SUM">10103.13</column></item></PivotSet>
(Note: I have slightly improved the readability of the output)
Instead of hardcoding the values of the PIVOT column, the PIVOT XML allows to dynamic forms of expressing the range of PIVOT column values:
- FOR pivot column in (ANY)
- FOR pivot column in (subquery)
Above you the first form – which is equivalent to this example of the second form:
for hireyear in (select distinct extract(year from hiredate) from emp)
Both can be very useful.
