The PIVOT Operator

PIVOT Query Plans

Craig Freedman's WebLog — Mon, 09 Jul 2007 21:23:58 GMT

In my last post , I gave an overview of the PIVOT operator. In this post, I'm going to take a look at

PIVOT Query Plans

Noticias externas — Mon, 09 Jul 2007 22:26:00 GMT

In my last post , I gave an overview of the PIVOT operator. In this post, I'm going to take a look

The UNPIVOT Operator

Craig Freedman's WebLog — Tue, 17 Jul 2007 20:44:08 GMT

The UNPIVOT operator is the opposite of the PIVOT operator. As I explained in my earlier post , the PIVOT

The UNPIVOT Operator

Noticias externas — Tue, 17 Jul 2007 21:09:24 GMT

The UNPIVOT operator is the opposite of the PIVOT operator. As I explained in my earlier post , the PIVOT

re: The PIVOT Operator

mmurrell — Sat, 28 Jul 2007 21:08:18 GMT

Great Post...

Is there any way to add row-level aggregations to the end? Following your example, I would like to add another column for Total Employee Sales that sums the years by employee.

And the parallel question would be can I add a row with column-level aggregations? For example, a new row with null employee id that sums the sales by year.

Any help would be appreciated.

re: The PIVOT Operator

craigfr — Wed, 01 Aug 2007 19:42:39 GMT

For your first question, the best I can do with PIVOT is:

SELECT EmpId, [2005], [2006], [2007], [0] AS [Total]

FROM (

SELECT EmpId, Yr, Sales FROM Sales

UNION ALL

SELECT EmpId, 0, Sum(Sales) FROM Sales GROUP BY EmpId

) AS s

PIVOT (SUM(Sales) FOR Yr IN ([2005], [2006], [2007], [0])) AS p

We can get a better plan by rewriting it without PIVOT as:

SELECT EmpId,

SUM(CASE WHEN Yr = 2005 THEN Sales END) AS [2005],

SUM(CASE WHEN Yr = 2006 THEN Sales END) AS [2006],

SUM(CASE WHEN Yr = 2007 THEN Sales END) AS [2007],

SUM(Sales) AS [Total]

FROM Sales

GROUP BY EmpId

For your second question, we can use GROUP BY WITH ROLLUP:

SELECT EmpId, SUM([2005]), SUM([2006]), SUM([2007])

FROM (SELECT EmpId, Yr, Sales FROM Sales) AS s

PIVOT (SUM(Sales) FOR Yr IN ([2005], [2006], [2007])) AS p

GROUP BY EmpId WITH ROLLUP

Perhaps there is a better solution (to either question) that another reader might suggest?