SQL Tips For Oracle Pivots

Share |

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, its 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 Unpivot 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

Technorati Tags: Oracle