Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Donald K. Burleson

Oracle Tips

Oracle pivot SQL Tips

Using Oracle 11g's Pivot and Unpivot Operator

By John Adolph Palinski

Every SQL writer has at times wanted to write a Select statement that presented the data horizontally as a series of columns rather than vertically as a series rows.  Displaying the data as a series of columns rather than roles is called pivoting.  In order to accomplish pivoting the SQL writer had to employ various coding techniques such as unioned Select statements or using the Case statement.  Oracle 11g has given us a new operator called Pivot that allows us to pivot data with a much smaller statement.  This article describes how to use this operator in your statements.

First let's look at some sample data.  Listing 1 portrays a Select statement that displays the number of employees in the various departments.  The statement displays the employee count as a series of rows.  Each row displays a particular department and the employee count.  Notice that an in-line view was needed to compute the employee count and the in-line view result set was joined to the Department table.  This is done because there are some departments without employees and a dense result set is desired.  The new Pivot operator also allows you to generate a dense result set, but in a different manner.

select department, nvl(cnt,0) cnt
from department
  left join (select fk_department, count(*) cnt
             from employee
             group by fk_department) on (department = fk_department) 

DEPARTMENT                 CNT  
---------------------      ------

WEL                        6

INT                        7

POL                        8

CEN                        0

5 rows selected

Traditionally data was pivoted using the Decode function, Case statements, or unioned Select statements.  The latter technique is illustrated in Listing 2.  The listing contains an in-line view that has a unioned select statement for each pivoted value (i.e distinct department value).  Each statement consists of an aggregation of the target value (i.e. employees).  The resulting aggregation is displayed in one of the statement's columns.  Each statement contains an expression for each of the pivoted columns, but only the column that pertains to the statement's Where clause value is populated.  The unioned result sets are then summarized by the outer statement creating the pivoted result set.  The main problem with this technique is the need to pre-define each column and select statement.   

select sum(int) int, sum(wel) wel, sum(cen) cen, sum(pol) pol
from
 (select count(*) INT, 0 WEL, 0 CEN, 0 POL
 from employee
 where fk_department = 'INT'
union
 select 0, count(*), 0, 0
 from employee
 where fk_department = 'WEL'
union
 select 0, 0, count(*), 0
 from employee
 where fk_department = 'CEN'
union
 select 0, 0, 0, count(*)
 from employee
 where fk_department = 'POL')

INT      WEL      CEN       POL  
-------- -------- --------- --------
 7       6         0        8    

1 rows selected

Oracle 11g has given us the Pivot and Unpivot operators.  These operators help in reducing the amount of code but do not solve the problem most developers would like solved, computing dynamic columns.  Developers have long wanted a statement that dynamically creates the needed columns rather than having to code for each value.  Unfortunately the new operator does not do this either.  However, the operators might help in reducing the amount of code that must be written.  For this reason, it’s good to look at them.

Below is a template of the Pivot operator along with a description of its parameters.  The Unpivot operator is shown later in this article.

XML                          - This parameter causes the result set to be returned in XML format
Aggregate function     - The aggregate function (i.e. sum, count, min, max)  used to compute the pivot matrix value.
Expression                - The aggregated column
Alias                         -  Aggregate column alias
Column_list               -  The column whose values determine the pivot location
Subquery                  -  The Pivot expression columns

PIVOT [XML] (<aggregate function> (expression) [AS<alias>]
FOR (<column_list>)
IN <subquery>)

Listing 3 depicts the Pivot operator.  The statement produces the same result set as the traditional statement shown in Listing 2.  The statement produces a row that displays a count of the INT, WEL, CEN, and POL department employees.  The statement is placed in an in-line view.  Notice that Fk_department values are placed in the Subquery parameter list.  The values in this list determine the columns in the Pivot table.  The select statement above the Pivot operator actually produces more Fk_department values than are listed in the Subquery parameter list.  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 

Finer aggregated matrix values can also be displayed by adding additional columns to the master Select list and not listing them in the Pivot clause.  The additional values that increase the aggregation grain are displayed on the Y-Axis.  This is shown in Listing 4.  The statement displays department wages by current position. The Fk_department values are displayed along the X-Axis as headings.  The current positions are displayed in the first column or Y-Axis.  The sum of wages values are displayed in the matrix area. Notice the statement is essentially the same as Listing 3 except for the inclusion of the Current_positions column.      

select 
from
 (select current_position, fk_department, wages
 from employee)
 pivot
 (sum(wages)
 for fk_department in ('INT', 'WEL', 'CEN', 'POL'));

CURRENT_POSITIO      'INT'         'WEL'         'CEN'         'POL'
---------------      ----------    ----------    ----------    ---------- 
VICE PRESIDENT       9500          8500
LABORER 3                          13000
SALESPERSON 1                                                   7500
JANITOR              9500
CLERK 2              14000
TREASURER                          12500
... 
SYSTEM ANALYST
TREASURER CLERK                                                 12000
CLERK 1                                                         25400
MAINT. MAN 2         10000
ADMINISTRATOR                                                   9800

The example statement shown in Listing 5 illustrates that multiple matrix aggregations can be 
included in the statement.  The Listing 4 statement was modified with the inclusion of a count value.
The statement now displays department wages and employee counts by current position.  
A numeric literal value was added to the select clause and a summary function added to the Pivot 
clause.  The additions are bolded.

Listing 5 - Department wages and employee count by current position
 

SQL> select *
  2  from
  3  (select current_position, fk_department, wages, 1 cnt
  4  from employee)
  5  pivot
  6  (sum(wages) as wages, sum(cnt) as count
  7    for fk_department in ('INT', 'WEL', 'CEN', 'POL'));

 

CURRENT_POSITIO 'INT'_WAGES     'INT'_COUNT     'WEL'_WAGES     'WEL'_COUNT     'CEN'_WAGES     'CEN'_COUNT     'POL'_WAGES     'POL'_COUNT
--------------- ------------    ------------    ------------    ------------    ------------    ------------    ------------    ------------
VICE PRESIDENT  9500            1               8500            1
LABORER 3                                       13000           1
SALESPERSON 1                                                                                                   7500            1
JANITOR         9500            1
CLERK 2         14000           1
... 
TREASURER CLERK                                                                                                 12000           1
CLERK 1                                                                                                         25400           2
MAINT. MAN 2    10000           1
ADMINISTRATOR                                                                                                   9800            1

Let's now look at the npivot operator. 

This operator does the opposite of the Pivot operator.  It takes values displayed on a single row and moves them into a series of rows.  A template of the operator follows as well as a description of the parameters.

Include|Exclude Nulls                 - Determines whether null column values are displayed as a row.  
Column list                                - The matix column
For column list                           - The heading for the X-Axis column values
In column list                             -  The X-Axis columns

UNPIVOT [<INCLUDE | EXCLUDE> NULLS] (<column_list>) FOR (<column_list>)
IN (<column_list>) [AS (<constant_list>)])

The first step is to generate a result set that can be unpivoted.  Listing 6 displays a statement that creates a view using one of the earlier example select statements (Listing 40.  The view creates a pivot table of department wages by current position.

Listing 6 - 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));

View created.

Now let's unpivot the result set.  Listing 7 shows a select statement with the unpivot operator. 

 The statement begins with a Select statement against the view created in Listing 6.  Notice the following:

1.  The "Include Nulls" option was used.  If a department current position did not have wages, the row is included anyway
2.  The parameter values in the column list were column names not literal values that we have seen in other examples.
3.  There are no summary functions used in the Unpivot clause.

Listing 7 - Using the Unpivot operator to create rows

SQL> select *

  2  from pivotTable

  3  unpivot include nulls

  4  (wages for fk_department in (INT, WEL, CEN, POL));

CURRENT_POSITIO                      FK_         WAGES

----------------------------        ---------   ----------

VICE PRESIDENT                     	INT           9500

VICE PRESIDENT                     	WEL           8500 

VICE PRESIDENT                     	CEN 
VICE PRESIDENT                     	POL 
LABORER 3                          	INT  
LABORER 3                          	WEL           13000 
LABORER 3                           CEN
...                             
TREASURER CLERK                    	INT 
TREASURER CLERK                      WEL    
TREASURER CLERK                      CEN    
TREASURER CLERK                      POL           12000 
CLERK 1                              INT  
CLERK 1                              WEL     
 

				   
				   
				   
				   
				   
				   
				   
				   
				   
				   
				   
				   
				   
				   
				   
				   The 
				   Pivot and Unpivot operators are not the ultimate.  I 
				   would have preferred having an operator that would allow a 
				   Select statement in the subquery parameter list that allows 
				   the developer to generate a dynamic list of columns.  
				   This would have made the Pivot operator a very effective 
				   tool.  However, I do think the Pivot and Unpivot operators will reduce some of the coding that is done to 
				   create Pivot tables from a result set.

				   
				   
				   
				   
				   
				   
				   
				   
				   
				   
				   
				   
				   
				   
				   
				   John 
				   Adolph Palinski is a former adjunct faculty member at the 
				   University of Nebraska at Omaha and Iowa Western Community College. 

				   
				   
				   
				   
				   
				   
				   
				   
				   
				   
				   
				   
				   
				   
				   
				   He is 
				   the author of the "Oracle Database Construction Kit", Que, 
				   1997, the "Oracle SQL and PL/SQL Handbook: A Guide for Data 
				   Administrators, Developers, and Business Analysts", Addison 
				   Wesley Longman, 2002, and "Oracle 9i Developer: Developing 
				   Web Applications With Forms Builder", Course Technology, 
				   2003.