So I get that I am a little late to the party here; however, I find myself having to pivot from time to time and am always looking back for code as a refresher. While pivot is pretty cool, it always takes me a second to connect all the dots so I hope this post help do that.
So, with Pivot, we want to take data that looks like the data on the right–there are multiple rows for each ProductId and would like to get each product ID on one line with the quantities from each Bin.
When we are finished, the data will look like this.
In my example, I am using the [Production].[ProductInventory] table in the adventureworks2012 database. To make a row become columns, we must know ALL possible values we want to make as a column. We specify these in the FOR line of the pivot syntax. This query will create the columns for each value I specified in the Shelf column
SELECT [ProductID],[A],[B],[C],[D],[E],[F],[G],[H],[I],[J],[K],[L] FROM [Production].[ProductInventory] PIVOT ( SUM (Quantity) FOR SHELF IN ([A],[B],[C],[D],[E],[F],[G],[H],[I],[J],[K],[L]) ) AS PVT
I should see something like this. We are getting closer–we have our columns, but we still a line for each record. We only want to see one line per product id. This is where we put the columns we want from the table and the pivot syntax together in an almost sub-query like format and select the columns as we want them to display.
The syntax is something like this. The first two rows are the columns I want, the second two are the columns I need from the table, and then add the pivot syntax followed by an order by and presto, you have your data the way you want it.
SELECT [ProductID],[A],[B],[C],[D],[E],[F],[G],[H],[I],[J],[K],[L] FROM (SELECT [ProductID], Shelf, Quantity FROM [Production].[ProductInventory]) p PIVOT ( SUM (Quantity) FOR SHELF IN ([A],[B],[C],[D],[E],[F],[G],[H],[I],[J],[K],[L]) ) AS PVT ORDER BY [ProductID]