SQL Tips For Oracle Pivots
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
