Recipe14.2.Unpivoting a Cross-Tab Report Using SQL Server s UNPIVOT Operator

Recipe 14.2. Unpivoting a Cross-Tab Report Using SQL Server's UNPIVOT Operator

Problem

You have a pivoted result set (or simply a fat table) and you wish to unpivot the result set. For example, instead of having a result set with one row and four columns you want to return a result set with two columns and four rows. Using the result set from the previous recipe, you want to convert it from this:

 ACCOUNTING   RESEARCH      SALES OPERATIONS ---------- ---------- ---------- ----------          3          5          6          0 

to this:

 DNAME                 CNT -------------- ---------- ACCOUNTING              3 RESEARCH                5 SALES                   6 OPERATIONS              0 

Solution

You didn't think SQL Server would give you the ability to PIVOT without being able to UNPIVOT, did you? To unpivot the result set just use it as the driver and let the UNPIVOT operator do all the work. All you need to do is specify the column names:

  1   select DNAME, CNT  2     from (  3       select [ACCOUNTING] as ACCOUNTING,  4              [SALES]      as SALES,  5              [RESEARCH]   as RESEARCH,  6              [OPERATIONS] as OPERATIONS  7         from (  8                 select d.dname, e.empno  9                   from emp e,dept d 10                  where e.deptno=d.deptno 11 12               ) driver 13         pivot ( 14           count(driver.empno) 15           for driver.dname in ([ACCOUNTING],[SALES],[RESEARCH],[OPERATIONS]) 16         ) as empPivot 17  )  new_driver 18  unpivot (cnt for dname in (ACCOUNTING,SALES,RESEARCH,OPERATIONS) 19  ) as un_pivot 

Hopefully, before reading this recipe you've read the one prior to it, because the inline view NEW_DRIVER is simply the code from the previous recipe (if you don't understand it, please refer to the previous recipe before looking at this one). Since lines 316 consist of code you've already seen, the only new syntax is on line 18, where you use UNPIVOT.

The UNPIVOT command simply looks at the result set from NEW_DRIVER and evaluates each column and row. For example, the UNPIVOT operator evaluates the column names from NEW_DRIVER. When it encounters ACCOUNTING, it transforms the column name ACCOUNTING into a row value (under the column DNAME). It also takes the value for ACCOUNTING from NEW_DRIVER (which is 3) and returns that as part of the ACCOUNTING row as well (under the column CNT). UNPIVOT does this for each of the items specified in the FOR list and simply returns each one as a row.

The new result set is now skinny and has two columns, DNAME and CNT, with four rows:

  select DNAME, CNT   from (     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 ) new_driver unpivot (cnt for dname in (ACCOUNTING,SALES,RESEARCH,OPERATIONS) ) as un_pivot DNAME                 CNT -------------- ---------- ACCOUNTING              3 RESEARCH                5 SALES                   6 OPERATIONS              0