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

Cross-Tab Reports with 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 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

We’ve used 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 here):

for driver.deptno in ( [10],[20],[30],[40] )
  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 here 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 want 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 you prefer using it to the traditional methods of pivoting, it’s nice to have another tool in your toolbox.

Вас заинтересует / Intresting for you:

A Brief History of SQL
A Brief History of SQL 3301 views Александров Попков Wed, 17 Oct 2018, 15:04:29
SQL: how to determine which ro...
SQL: how to determine which ro... 245 views Денис Tue, 06 Jul 2021, 18:47:53
Referencing an Aliased Column ...
Referencing an Aliased Column ... 567 views Денис Wed, 14 Jul 2021, 12:59:03
How to Retrieving a Subset of ...
How to Retrieving a Subset of ... 395 views Денис Wed, 14 Jul 2021, 04:45:37