T-SQL Recipes dynamic PIVOT and UNPIVOT

PIVOT

pivot is often used when it comes to columns, and the most convenient way to do this is to understand its usefulness through examples.

Example 1 Query to Return Select Product Data from AdventureWorks

SELECT   as Product_Name,          as Product_color,        Product_inventory. LocationID,        PRODUCT. ReorderPoint,        as  product_quantityfrom    production.product PRODUCT        leftJOINon= product_inventory. ProductID;

View Code

Results:

What if we want Product_coor to show the number of each product in the column? That's when pivot comes out. Example 2:common use of pivot to report on products by Color

 withProduct_data as(SELECTPRODUCT. Name asproduct_name, product. Color asProduct_color, product. ReorderPoint, Product_inventory. Quantity asproduct_quantity fromproduction.product Product Left JOINProduction.productinventory product_inventory onPRODUCT. ProductID=product_inventory. ProductID)SELECT  *     fromProduct_data PIVOT (SUM(product_quantity) forProduct_colorinch([Black],[Blue],[Grey],                                               [Multi],[Red],[Silver],                                               [Silver/black],[ White],                                               [Yellow])) Pivot_data;

View Code

Results:

You can see that pivot has two steps from SQL

• An aggregate function, which would aggregate if multiple values exist. In the initial
  SELECT statement that returns product data, there were many duplicate rows. This
  Example uses SUM whenever this occurs, which would add up product quantities if there
  is multiple rows with the same product name.

• A Value list for any values that would be is changed from row data into column headers. Inch
  This case, the list is of colors from Product.color.

PS: Although solve the problem of row to column, but this time, we should know how many unique color inside the only data, if we do not know how to solve the situation? This is when dynamic SQL comes in.

Example 3:common use of the PIVOT to report on products by Color

 UseAdventureWorks2014;GODECLARE @sql_command NVARCHAR(MAX);DECLARE @sql_colors NVARCHAR( +);SET @sql_command = 'With Product_data as (SELECT PRODUCT. Name as Product_Name, product. Color as Product_color, product. ReorderPoint, Product_inventory. Quantity as product_quantity from production.product product left JOIN Production . ProductInventory product_inventory on PRODUCT. ProductID = Product_inventory. ProductID) SELECT * from Product_data PIVOT (SUM (product_quantity) for Product_color in ('; withcolorlist as(SELECT DISTINCTProduct.color asColor_name fromproduction.productWHEREProduct.color is  not NULL             )    SELECT  @sql_colors = ISNULL(@sql_colorsN"')+N','            + QUOTENAME(Color_name) fromcolorlist;SET @sql_colors = STUFF(@sql_colors,1,1,"');SET @sql_command = @sql_command + @sql_colors +N')) Pivot_data';PRINT @sql_command;EXECsp_executesql@sql_command;

View Code

First look at the printed sql:

 withProduct_data as(SELECTPRODUCT. Name asproduct_name, product. Color asProduct_color, product. ReorderPoint, Product_inventory. Quantity asproduct_quantity fromproduction.product Product Left JOINProduction.productinventory product_inventory onPRODUCT. ProductID=product_inventory. ProductID)SELECT  *     fromProduct_data PIVOT (SUM(product_quantity) forProduct_colorinch([Black],[Blue],[Grey],                                               [Multi],[Red],[Silver],                                               [Silver/black],[ White],                                               [Yellow])) Pivot_data

View Code

Results:

From Example 1 To Example 3, we learned how to break down complex SQL and finally combine them.

UNPIVOT

Ds

Ds

T-SQL Recipes dynamic PIVOT and UNPIVOT