Yes, it is here too: the PIVOT operator in Oracle 11g

0

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 />&nbsp;

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&gt; 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 />&nbsp;

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&gt; 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 />&nbsp;

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 />,      &quot;1&quot; as Monday<br />,      &quot;2&quot; as Tuesday<br />,      &quot;3&quot; as Wednesday<br />,      &quot;4&quot; as Thursday<br />,      &quot;5&quot; 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 />&nbsp;

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 />&nbsp;

output

    DEPTNO   HIREYEAR_XML<br />--------------------------------------------------------------------------------------------------------------<br />        10   &lt;PivotSet&gt;&lt;item&gt;&lt;column name = &quot;HIREYEAR&quot;&gt;1981&lt;/column&gt;&lt;column name = &quot;COUNT_EMPS&quot;&gt;2&lt;/column&gt;<br />                             &lt;column name = &quot;SAL_SUM&quot;&gt;7450&lt;/column&gt;&lt;/item&gt;<br />                       &lt;item&gt;&lt;column name = &quot;HIREYEAR&quot;&gt;1982&lt;/column&gt;&lt;column name = &quot;COUNT_EMPS&qu
ot;&gt;1&lt;/column&gt;<br /
>                             &lt;column name = &quot;SAL_SUM&quot;&gt;1300&lt;/column&gt;&lt;/item&gt;<br />             &lt;/PivotSet&gt;<br /><br />        20   &lt;PivotSet&gt;&lt;item&gt;&lt;column name = &quot;HIREYEAR&quot;&gt;1980&lt;/column&gt;&lt;column name = &quot;COUNT_EMPS&quot;&gt;1&lt;/column&gt;<br />                             &lt;column name = &quot;SAL_SUM&quot;&gt;800&lt;/column&gt;&lt;/item&gt;<br />                       &lt;item&gt;&lt;column name = &quot;HIREYEAR&quot;&gt;1981&lt;/column&gt;&lt;column name = &quot;COUNT_EMPS&quot;&gt;2&lt;/column&gt; <br />                             &lt;column name = &quot;SAL_SUM&quot;&gt;5975&lt;/column&gt;&lt;/item&gt;<br />                       &lt;item&gt;&lt;column name = &quot;HIREYEAR&quot;&gt;1987&lt;/column&gt;&lt;column name = &quot;COUNT_EMPS&quot;&gt;2&lt;/column&gt;<br />                             &lt;column name = &quot;SAL_SUM&quot;&gt;4850&lt;/column&gt;&lt;/item&gt;<br />             &lt;/PivotSet&gt;<br /><br />        30<br />&lt;PivotSet&gt;&lt;item&gt;&lt;column name = &quot;HIREYEAR&quot;&gt;1981&lt;/column&gt;&lt;column name = &quot;COUNT_EMPS&quot;&gt;6&lt;/column&gt;&lt;column name = &quot;S<br />AL_SUM&quot;&gt;10103.13&lt;/column&gt;&lt;/item&gt;&lt;/PivotSet&gt;<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.

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

Comments are closed.