Please explain the parts of a PIVOT

Question

I have read lots of blog posts. I have read the docs. I am usually fairly good at picking up new stuff but even though I keep reading, but I just don't understand the parts of a PIVOT in SQL Server (2008).

Can someone please give it to me, nice and slow. (ie Pivot for Dummies)

If an example is needed then we can use the one in this question.

Here is how I tried to pivot that example:

SELECT OtherID, Val1, Val2, Val3, Val4, Val5
FROM 
    (SELECT OtherID, Val
    FROM @randomTable) p
PIVOT
(
    max(val)
    FOR Val IN (Val1, Val2, Val3, Val4, Val5)
) AS PivotTable;

The above query gives me nulls instead of values in the Val1, Val2... columns.

But to be clear, I am not looking for a fixed query here. I need to understand PIVOT as I am looking to pivot something far more complex than this example.

Specifically what is the deal with the aggregate? I just want to take all string values that match on a given ID and put them in the same row. I am not trying to aggregate anything. (Again, see this question for my example.)

Answer 1

Explanation of the pivot query

FROM 
    (SELECT OtherID, Val, amount
    FROM @randomTable) p

These are the columns that become the "base data" for the pivot. Do not include columns that don't do anything. Just as you don't put non-GROUP BY columns into the SELECT clause, you don't list out unused columns in a PIVOT source.

PIVOT
(
    max(amount)
    FOR Val IN (Val1, Val2, Val3, Val4, Val5)
) AS PivotTable;

This part says that you are creating 5 new columns named "Val1" through "Val5". These column names represent values in the column Val. So it is expected that your table will contain something like this

otherID   Val     amount
1         Val1    1
2         Val2    2
1         Val3    3
1         Val1    5
(etc)     (this column contains one of Val1 - Val5, or null)

So you now have 5 new columns that did not exist before. What goes into the column?

• Any column that appears in the OUTPUT that is not a PIVOTed column is a "GROUP BY" column.
• The aggregate function is what collects all the data into the cell that is the CROSS between the GROUP BY columns and the PIVOTED column.

So, to illustrate, using the sample data above, we have otherID=1 and val=Val1. In the output table, there is only one cell representing this combination of Max(amount) for each (otherID/val) combination

otherID   Val1   Val2   Val3   Val4   Val5
1         <x>    ...    ...    ...    ...
(etc)

For the cell marked <x> , only one value is allowed, so <x> cannot contain multiple amount values. That is the reason why we need to aggregate it, in this case using MAX(amount). So in fact, the output looks like this

(unpivoted columns)   (pivoted, creates "new" columns)
otherID             |  Val1          Val2           Val3   Val4   Val5
1                   |  MAX(amount)   Max(amount)    << cell value = aggregate function
(etc)

The SELECT statement is what then outputs these columns

SELECT OtherID, Val1, Val2, Val3, Val4, Val5