Problem
You want to create a cross-tab report to transform your result set’s rows into columns.
You are aware of traditional methods of pivoting but would like to try something different. In particular, you want to return the following result set without using CASE
expressions or joins:
DEPT_10 DEPT_20 DEPT_30 DEPT_40
------- ----------- ----------- ----------
3 5 6 0
Solution
Use the PIVOT
operator to create the required result set without CASE
expressions or additional joins:
1 select [10] as dept_10,
2 [20] as dept_20,
3 [30] as dept_30,
4 [40] as dept_40
5 from (select deptno, empno from emp) driver
6 pivot (
7 count(driver.empno)
8 for driver.deptno in ( [10],[20],[30],[40] )
9 ) as empPivot
Discussion
The PIVOT
operator may seem strange at first, but the operation it performs in the solution is technically the same as the more familiar transposition query shown here:
select sum(case deptno when 10 then 1 else 0 end) as dept_10,
sum(case deptno when 20 then 1 else 0 end) as dept_20,
sum(case deptno when 30 then 1 else 0 end) as dept_30,
sum(case deptno when 40 then 1 else 0 end) as dept_40
from emp
DEPT_10 DEPT_20 DEPT_30 DEPT_40
------- ---------- ---------- ----------
3 5 6 0
Now that you know what is essentially happening, let’s break down what the PIVOT
operator is doing. Line 5 of the solution shows an inline view named DRIVER
: from (select deptno, empno from emp) driver
We’ve used the alias DRIVER
because the rows from this inline view (or table expression) feed directly into the PIVOT
operation. The PIVOT
operator rotates the rows to columns by evaluating the items listed on line 8 in the FOR
list (shown here):
for driver.deptno in ( [10],[20],[30],[40] )
- If there are any DEPTNOs with a value of 10, perform the aggregate operation defined (
COUNT(DRIVER.EMPNO)
) for those rows. - Repeat for DEPTNOs 20, 30, and 40.
The items listed in the brackets on line 8 serve not only to define values for which aggregation is performed; the items also become the column names in the result set (without the square brackets). In the SELECT
clause of the solution, the items in the FOR
list are referenced and aliased. If you do not alias the items in the FOR
list, the column names become the items in the FOR
list sans brackets.
Interestingly enough, since inline view DRIVER
is just that—an inline view—you may put more complex SQL in there. For example, consider the situation where you want to modify the result set such that the actual department name is the name of the column. Listed here are the rows in table DEPT:
select * from dept
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
You want to use PIVOT
to return the following result set:
ACCOUNTING RESEARCH SALES OPERATIONS
---------- ---------- ---------- ----------
3 5 6 0
Because inline view DRIVER
can be practically any valid table expression, you can perform the join from table EMP to table DEPT and then have PIVOT
evaluate those rows. The following query will return the desired result set:
select [ACCOUNTING] as ACCOUNTING,
[SALES] as SALES,
[RESEARCH] as RESEARCH,
[OPERATIONS] as OPERATIONS
from (
select d.dname, e.empno
from emp e,dept d
where e.deptno=d.deptno
) driver
pivot (
count(driver.empno)
for driver.dname in ([ACCOUNTING],[SALES],[RESEARCH],[OPERATIONS])
) as empPivot
As you can see, PIVOT
provides an interesting spin on pivoting result sets. Regardless of whether you prefer using it to the traditional methods of pivoting, it’s nice to have another tool in your toolbox.