Recipe14.1.Creating Cross-Tab Reports Using SQL Server s PIVOT Operator

Recipe 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 below:

  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 

I've chosen 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 below):

 for driver.deptno in ( [10],[20],[30],[40] ) 

The evaluation goes something like this:

1. If there are any DEPTNOs with a value of 10, perform the aggregate operation defined ( COUNT(DRIVER.EMPNO) ) for those rows.

2. 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 below 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 would like 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 or not you prefer using it to the traditional methods of pivoting, it's nice to have another tool in your toolbox.