SQL Cookbook, 2nd Edition

Chapter 14. Odds ’n’ Ends

This chapter contains queries that didn’t fit in any other chapter, either because the chapter they would belong to is already long enough, or because the problems they solve are more fun than realistic. This chapter is meant to be a “fun” chapter, in that the recipes here may or may not be recipes that you would actually use; nevertheless, the queries are interesting, and we wanted to include them in this book.

14.1 Creating Cross-Tab Reports Using SQL Server’s PIVOT Operator

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