T-SQL Recipes之Dynamic PIVOT and UNPIVOT

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

PIVOT

PIVOT在行转列的时候经常用到，最便捷的方式就是通过示例来理解它的作用。

示例1 Query to Return Select Product Data from AdventureWorks

SELECT PRODUCT.Name AS product_name ,
PRODUCT.Color AS product_color ,
PRODUCT_INVENTORY.LocationID ,
PRODUCT.ReorderPoint ,
PRODUCT_INVENTORY.Quantity AS product_quantity
FROM Production.Product PRODUCT
LEFT JOIN Production.ProductInventory PRODUCT_INVENTORY ON PRODUCT.ProductID = PRODUCT_INVENTORY.ProductID;

结果：

如果我们想要product_coor 在列里面显示每个产品的数量呢？这时候PIVOT就出场了

示例2：Common Use of PIVOT to Report on Products by Color

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 ( [Black], [Blue], [Grey],
[Multi], [Red], [Silver],
[Silver/Black], [White],
[Yellow] ) ) PIVOT_DATA;

结果：

从SQL中可以看出PIVOT有两个步骤
An aggregate function, which will 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 will add up product quantities if there
are multiple rows with the same product name.
A value list for all values that will be changed from row data into column headers. In
this case, the list is of colors from Product.Color .
PS:虽然解决了行转列的问题，但这个时候，我们应该知道color里面到底有多少条唯一的数据，如果在我们不知的情况下，如何解决呢？这个时候动态SQL就来了。

示例3：Common Use of PIVOT to Report on Products by Color

USE AdventureWorks2014;
GODECLARE @sql_command NVARCHAR(MAX);
DECLARE @sql_colors NVARCHAR(1000);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 (';
WITH colorlist
AS ( SELECT DISTINCT
Product.Color AS color_name
FROM Production.Product
WHERE Product.Color IS NOT NULL
)
SELECT @sql_colors = ISNULL(@sql_colors, N'') + N','
+ QUOTENAME(color_name)
FROM colorlist;SET @sql_colors = STUFF(@sql_colors, 1, 1, '');SET @sql_command = @sql_command + @sql_colors + N' )) PIVOT_DATA';PRINT @sql_command;
EXEC sp_executesql @sql_command;

首先看一下打印出来的SQL：

结果：

从示例1到示例3，我们从中了解到如何把复杂的SQL慢慢分解出来，最后在组合在一起。

UNPIVOT

顾名思义，就是PIVOT的反向操作：列转行。我们还是从简单的示例到复杂的示例看慢慢了解。
PS:为了方便，就直接用上面的数据来做列转行。

示例1：Using UNPIVOT to Revert Column Headers into Row Data

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
),
PRODUCTS_BY_COLOR
AS ( SELECT *
FROM PRODUCT_DATA PIVOT
( SUM(product_quantity) FOR product_color IN ( [Black], [Blue], [Grey],
[Multi], [Red], [Silver],
[Silver/Black], [White],
[Yellow] ) ) PIVOT_DATA
)
SELECT *
FROM PRODUCTS_BY_COLOR UNPIVOT
( product_quantity FOR Color IN ( [Black], [Blue], [Grey], [Multi], [Red],
[Silver], [Silver/Black], [White], [Yellow] ) ) AS UNPIVOT_DATA;

从SQL中可以看出，UNPIVOT同样有两个步骤，这里就不详说了，可以查看MSND了解更多。
PS:我们必须指定列转行里面的字段，如果数据库增加了一个color，那我们必须要更改这段SQL，除非我们用动态SQL来实现。

示例2： A Dynamic UNPIVOT Using Original Row Data to Supply Color Names

USE AdventureWorks2014;
GODECLARE @sql_command NVARCHAR(MAX);
DECLARE @sql_pivotcommand NVARCHAR(MAX);
DECLARE @sql_unpivotcommand NVARCHAR(MAX);DECLARE @sql_colors NVARCHAR(1000);SET @sql_pivotcommand = '
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
),
PRODUCTS_BY_COLOR
AS ( SELECT *
FROM PRODUCT_DATA PIVOT ( SUM(product_quantity) FOR product_color IN (';
WITH colorlist
AS ( SELECT DISTINCT
Product.Color AS color_name
FROM Production.Product
WHERE Product.Color IS NOT NULL
)
SELECT @sql_colors = ISNULL(@sql_colors, N'') + N','
+ QUOTENAME(color_name)
FROM colorlist;SET @sql_colors = STUFF(@sql_colors, 1, 1, '');SET @sql_pivotcommand = @sql_pivotcommand + @sql_colors + N' )) PIVOT_DATA)';SET @sql_unpivotcommand = ' SELECT *
FROM PRODUCTS_BY_COLOR UNPIVOT ( product_quantity FOR Color IN (' + @sql_colors + ') ) AS UNPIVOT_DATA';SET @sql_command = @sql_pivotcommand + @sql_unpivotcommand;PRINT @sql_command;
EXEC sp_executesql @sql_command;

打印出来的SQL:

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
),
PRODUCTS_BY_COLOR
AS ( SELECT *
FROM PRODUCT_DATA PIVOT
( SUM(product_quantity) FOR product_color IN ( [Black], [Blue], [Grey],
[Multi], [Red], [Silver],
[Silver/Black], [White],
[Yellow] ) ) PIVOT_DATA
)
SELECT *
FROM PRODUCTS_BY_COLOR UNPIVOT ( product_quantity FOR Color IN (
[Black], [Blue], [Grey],
[Multi], [Red], [Silver],
[Silver/Black], [White],
[Yellow] ) ) AS UNPIVOT_DATA