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, I consider the queries interesting and wanted to include them somewhere in this book.
14.1. Creating Cross-Tab Reports Using SQL Server’s PIVOT Operator
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
Use the PIVOT operator to create the required result set without CASE expressions or additional joins:
1 select  as dept_10, 2  as dept_20, 3  as dept_30, 4  as dept_40 5 from (select deptno, empno from emp) driver 6 pivot ( 7 count(driver.empno) 8 for driver.deptno in ( ,,, ) 9 ) as empPivot
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 below:
select sum(case deptno when 10 then 1 else 0 end) as dept_10,
sum(case deptno when 20 then 1 else 0 ...