sql pivot unpivot - ghdrako/doc_snipets GitHub Wiki

A PIVOT operator, simply put, transforms a table output column into rows. It rotates a table-valued expression (a table-valued expression returns output as a result set/table) by turning unique values from a selected column into multiple columns and aggregates the remaining column values in the fi nal table output.

h e PIVOT operator is very similar to a CASE statement, but much simpler and more easily readable for the user. Here is the syntax for it:

SELECT <Unique Column Name(s)> FROM <SELECT query to produce data from table(s)> PIVOT ( <column to be aggregated, e.g., COUNT, AVG, etc.> FOR <Unique Column values that will become column headers> );

Important For aggregate functions in the PIVOT operator, any null values in the value column are not considered during computation.

SELECT 'Total Purchase Orders' AS TotalPurchaseOrders , [1] AS SalesPer1, [2] AS SalesPer2, [4] AS SalesPer3 , [5] AS SalesPer4, [7] AS SalesPer5, [10] AS SalesPer6 , [12] AS SalesPer7 FROM ( SELECT [SupplierID] , [PurchaseOrderID] FROM [Purchasing].[PurchaseOrders] ) AS SrcTbl PIVOT ( COUNT([PurchaseOrderID]) FOR [SupplierID] IN ([1], [2], [4], [5], [7], [10], [12]) ) AS PvtTbl;

Here the unique SupplierID values are aliased to SalesPer1, SalesPer2…, and so on.

The UNPIVOT operator performs the complete opposite operation of the PIVOT operator. It rotates columns into rows.

Important If you compare both PIVOT and UNPIVOT inputs and outputs, UNPIVOT is not the exact opposite of PIVOT. PIVOT does aggregate and merge similar supplier IDs into one record but when you run UNPIVOT, it won’t get back to the original table since the aggregation information is not captured. Also, any NULL value columns will be fi ltered in the fi nal UNPIVOT output (for example, if we had any NULL PurchaseOrder instances in the preceding query, they won’t show up in the output UNPIVOT table.