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


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:

	---------  ----------  ----------
	        3           5           6


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


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 ...

Get SQL Cookbook now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.