The PIVOT Operator

re: The PIVOT Operator

Craig Freedman — Mon, 31 Oct 2011 14:15:58 GMT

Yes, joins are possible in the source query which can be any legal SQL query.

re: The PIVOT Operator

karthick — Sat, 29 Oct 2011 11:24:17 GMT

is it possible to use join statements in pivot operator ?

re: The PIVOT Operator

Craig Freedman — Tue, 22 Jun 2010 20:33:25 GMT

Use ROLLUP or GROUPING SETS. See blogs.msdn.com/.../aggregation-with-rollup.aspx or blogs.msdn.com/.../grouping-sets-in-sql-server-2008.aspx.

re: The PIVOT Operator

Roni — Fri, 18 Jun 2010 10:02:17 GMT

it's a great topic,

hm..I want to make table like this using pivot, is that posible? thx b4

StudentName | AssignmentName | Grade

---------------------------------------

StudentA | Assignment 1 | 100

StudentA | Assignment 2 | 80

StudentA | Total | 180

StudentB | Assignment 1 | 100

StudentB | Assignment 2 | 80

StudentB | Assignment 3 | 100

StudentB | Total | 280

re: The PIVOT Operator

Craig Freedman — 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?

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.

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

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

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

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