Pivotal Pivoting

Looking over pivoting again this morning.  Trying to make sense of it and running through a few homework sets so that I can play around with them to generate errors and the like.

A few key things to remember, the grouping element is what you want to see on the rows of the table, the spreading element should be the columns, this needs to be defined.  The Pivot syntax as I begin to remember it goes like this

With cte as (

SELECT grouping column, spreading column, aggregate

FROM table)

SELECT grouping element

FROM cte

PIVOT(Aggregate function --like sum, count, min, max, etc) FOR spreading column IN (distinct value)

From the example that I wrote yesterday from the blog http://terrychoo.com/understanding-the-pivot-function/ I added in more data to make more sense of everything.  Here is the data that I added into the @orders table (

INSERT INTO @orders (custid, empid, shipperid, freight)
VALUES (87,3,2,6.0), (87, 3, 2, 4.0)
--highlighted numbers will be aggregated because they are freight value.

Since we're trying to find the total freight for each customerid, with the shipperid like 1, 2, or 3, when you add in another set of values (or two) you can see how the pivot is working because you can see the aggregation for the customerid that is 85, and the aggregation when the customerid is 87.  Here is the result set (pardon the spacing, it was done free handed):

custid      1                 2              3
-------------------------------------------
85         6.01            8.94 43.46

87       NULL        10.00 NULL

Now it's more clear which elements are spreading and grouping… we're grouping on customerid (that's why only one custid appears) and we're spreading with shipperid, then the aggregation appears as the intersection of the two.

The last note on this as I continue to play with is is that you must identify unique values for the "IN" clause… you can't just say, "FOR <spreading clause>;" this returns and error.  Also, I thought that the square brackets weren't necessary but it appears they are as I generated an error when just putting numbers in the IN clause, or when I put the spreading elements in single quotes… 

Next we UNPIVOT!

Cheers!