Oracle pivot SQL Tips
Oracle 11g's Pivot and Unpivot Operator
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
left join (select fk_department, count(*) cnt
group by fk_department) on (department = fk_department)
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
select sum(int) int, sum(wel) wel, sum(cen) cen, sum(pol)
(select count(*) INT, 0 WEL, 0 CEN, 0 POL
where fk_department = 'INT'
select 0, count(*), 0, 0
fk_department = 'WEL'
select 0, 0, count(*), 0
where fk_department = 'CEN'
select 0, 0, 0, count(*)
fk_department = 'POL')
-------- -------- --------- --------
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.
- This parameter causes the result set to be returned in XML
Aggregate function - The
aggregate function (i.e. sum, count, min, max) used to
compute the pivot matrix value.
- The aggregated column
- Aggregate column alias
- The column whose values determine the pivot location
- The Pivot expression columns
(<aggregate function> (expression) [AS<alias>]
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.
fk_department in ('INT', 'WEL', 'CEN', 'POL'));
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
(select current_position, fk_department, 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
CLERK 2 14000
TREASURER CLERK 12000
CLERK 1 25400
MAINT. MAN 2 10000
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 *
3 (select current_position, fk_department, wages, 1 cnt
4 from employee)
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.
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.
- Determines whether null column values are displayed as a
- 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>)
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 current_position, fk_department, wages
for fk_department in ('INT' as int, 'WEL' as wel,
'CEN' as cen, 'POL' as pol));
let's unpivot the result set. Listing 7 shows a select
statement with the unpivot operator.
statement begins with a Select statement against the view
created in Listing 6. Notice the following:
The "Include Nulls" option was used. If a department
current position did not have wages, the row is included
2. The parameter values in the column list
were column names not literal values that we have seen in
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
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.
Adolph Palinski is a former adjunct faculty member at the
University of Nebraska at Omaha and Iowa Western Community College.
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,