SQL Cookbook

Chapter 12. Reporting and Warehousing

This chapter introduces queries you may find helpful for creating reports. These typically involve reporting-specific formatting considerations along with different levels of aggregation. Another focus of this chapter is on transposing or pivoting result sets, converting rows into columns. Pivoting is an extremely useful technique for solving a variety of problems. As your comfort level increases with pivoting, you’ll undoubtedly find uses for it outside of what are presented in this chapter.

12.1. Pivoting a Result Set into One Row

Problem

You wish to take values from groups of rows and turn those values into columns in a single row per group. For example, you have a result set displaying the number of employees in each department:

	DEPTNO        CNT
	------ ----------
	    10          3
	    20          5
	    30          6

You would like to reformat the output such the result set looks as follows:

	DEPTNO_10   DEPTNO_20   DEPTNO_30
	---------  ----------  ----------
	        3           5           6

Solution

Transpose the result set using a CASE expression and the aggregate function SUM:

	1 select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
	2        sum(case when deptno=20 then 1 else 0 end) as deptno_20,
	3        sum(case when deptno=30 then 1 else 0 end) as deptno_30
	4   from emp

Discussion

This example is an excellent introduction to pivoting. The concept is simple: for each row returned by the unpivoted query, use a CASE expression to separate the rows into columns. Then, because this particular problem is to count the number of employees ...