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:http://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 *<br />from (select deptno<br /> , extract (year from hiredate) hireyear<br /> from emp<br /> ) pivot ( count(hireyear) count_emps<br /> for hireyear in (1980, 1981, 1982) <br /> )<br />/<br /> DEPTNO 1980_COUNT_EMPS 1981_COUNT_EMPS 1982_COUNT_EMPS<br />---------- --------------- --------------- ---------------<br /> 30 0 6 0<br /> 20 1 2 0<br /> 10 0 2 1<br /><br />3 rows selected.<br /><img title="The remainder of this blog post is shown after " alt="...." src="./wp-content/plugins/xinha4wp/xinha_core/plugins/InsertMore/img/ed_more.png" /><span id="more-2425"></span><br />

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 *<br /> 2 from (select deptno<br /> 3 , extract (year from hiredate) hireyear<br /> 4 , sal<br /> 5 from emp<br /> 6 ) pivot ( count(hireyear) count_emps<br /> 7 , sum(sal) sal_sum<br /> 8 for hireyear in (1980, 1981, 1982)<br /> 9 )<br /> 10 /<br /><br /> DEPTNO 1980_COUNT_EMPS 1980_SAL_SUM 1981_COUNT_EMPS 1981_SAL_SUM 1982_COUNT_EMPS 1982_SAL_SUM<br />---------- --------------- ------------ --------------- ------------ --------------- ------------<br /> 30 0 6 10103.13 0<br /> 20 1 800 2 5975 0<br /> 10 0 2 7450 1 1300<br /><br />3 rows selected.<br /><br />

This query adds a little bit extra: it gives us column totals under our matrix report:

select *<br />from (select deptno<br /> , sal<br /> , extract (year from hiredate) hireyear<br /> , 1 num<br /> from emp<br /> union all<br /> select to_number(null) deptno<br /> , avg(sal) avg_sal<br /> , extract (year from hiredate) hireyear<br /> , count(*) num<br /> from emp<br /> where extract (year from hiredate) in (1980, 1981, 1982)<br /> group <br /> by to_number(null) ,extract (year from hiredate)<br /> ) pivot ( sum(num) count_emps<br /> , avg(sal) sal_avg<br /> for hireyear in (1980, 1981, 1982) <br /> )<br />order<br />by deptno<br />/<br /><br /> DEPTNO 1980_COUNT_EMPS 1980_SAL_AVG 1981_COUNT_EMPS 1981_SAL_AVG 1982_COUNT_EMPS 1982_SAL_AVG<br />---------- --------------- ------------ --------------- ------------ --------------- ------------<br /> 10 2 3725 1 1300<br /> 20 1 800 2 2987.5<br /> 30 6 1683.855<br /> 1 800 10 2352.813 1 1300<br /><br />4 rows selected.<br />

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 *<br /> 2 from worked_hours<br /> 3 /<br /><br /> EMPNO DAY HOURS<br />---------- ---------- ----------<br /> 1210 1 8<br /> 1215 1 2<br /> 1210 2 7.5<br /> 1215 2 7.5<br /> 1210 3 8.5<br /> 1215 3 8<br /> 1210 4 4.5<br /> 1215 4 7.5<br /> 1210 5 8<br /> 1215 5 8<br />

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<br />, "1" as Monday<br />, "2" as Tuesday<br />, "3" as Wednesday<br />, "4" as Thursday<br />, "5" as Friday<br />from (select empno<br /> , hours<br /> , day<br /> from worked_hours<br /> ) pivot ( sum(hours)<br /> for day in (1,2,3,4,5)<br /> )<br />/<br /><br /> EMPNO MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY<br />---------- ---------- ---------- ---------- ---------- ----------<br /> 1215 2 7.5 8 7.5 8<br /> 1210 8 7.5 8.5 4.5 8<br /><br />2 rows selected.<br />

### 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 *<br />from (select deptno<br /> , extract (year from hiredate) hireyear<br /> , sal <br /> from emp<br /> ) pivot xml ( count(hireyear) count_emps<br /> , sum(sal) sal_sum <br /> for hireyear in (any)<br /> )<br />/<br />

output

DEPTNO HIREYEAR_XML<br />--------------------------------------------------------------------------------------------------------------<br /> 10 <PivotSet><item><column name = "HIREYEAR">1981</column><column name = "COUNT_EMPS">2</column><br /> <column name = "SAL_SUM">7450</column></item><br /> <item><column name = "HIREYEAR">1982</column><column name = "COUNT_EMPS&qu ot;>1</column><br / > <column name = "SAL_SUM">1300</column></item><br /> </PivotSet><br /><br /> 20 <PivotSet><item><column name = "HIREYEAR">1980</column><column name = "COUNT_EMPS">1</column><br /> <column name = "SAL_SUM">800</column></item><br /> <item><column name = "HIREYEAR">1981</column><column name = "COUNT_EMPS">2</column> <br /> <column name = "SAL_SUM">5975</column></item><br /> <item><column name = "HIREYEAR">1987</column><column name = "COUNT_EMPS">2</column><br /> <column name = "SAL_SUM">4850</column></item><br /> </PivotSet><br /><br /> 30<br /><PivotSet><item><column name = "HIREYEAR">1981</column><column name = "COUNT_EMPS">6</column><column name = "S<br />AL_SUM">10103.13</column></item></PivotSet><br />

(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) <br />

Both can be very useful.