T-SQL Part IX, PIVOT and UNPIVOT

不同于CROSS JOIN, CROSS APPLY, OUTER APPLY，MSDN文档对PIVOT和UNPIVOT 想得重视了一点，单独做了一个页面来介绍。
简单来说，PIVOT用来把行转成列，而UNPIVOT可以把列转成行。

用MSDN文档给出的两个例子来做说明。
例一，基础示例。

-- Pivot table with one row and five columns  SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,   [0], [1], [2], [3], [4]  FROM  (SELECT DaysToManufacture, StandardCost       FROM Production.Product) AS SourceTable  PIVOT  (  AVG(StandardCost)  FOR DaysToManufacture IN ([0], [1], [2], [3], [4])  ) AS PivotTable;  

例二，复杂示例。

USE AdventureWorks2014;  GO  SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5  FROM   (SELECT PurchaseOrderID, EmployeeID, VendorID  FROM Purchasing.PurchaseOrderHeader) p  PIVOT  (  COUNT (PurchaseOrderID)  FOR EmployeeID IN  ( [250], [251], [256], [257], [260] )  ) AS pvt  ORDER BY pvt.VendorID;  

第一步，也是所有SELECT语句的第一步，弄清楚Source Table，即FROM后面的源table。
对于例一：

(SELECT DaysToManufacture, StandardCost       FROM Production.Product) AS SourceTable  

对于例二：

(SELECT PurchaseOrderID, EmployeeID, VendorID  FROM Purchasing.PurchaseOrderHeader) p

第二步，理解PIVOT的作用域。
对于例一，即针对DaysToManufacture在IN范畴中的每个值计算AVG(StandardCost)

(  AVG(StandardCost)  FOR DaysToManufacture IN ([0], [1], [2], [3], [4])  )

对于例二，即针对EmployID在IN范畴中的每一个值计算COUNT (PurchaseOrderID)：

(  COUNT (PurchaseOrderID)  FOR EmployeeID IN  ( [250], [251], [256], [257], [260] )  )

事实上，可以用等价的SQL来实现PIVOT。以下两段SQL是等价的：

SELECT empid, [2013], [2014], [2015]FROM ( SELECT empid, YEAR(orderdate) AS orderyear, val       FROM Sales.OrderValues                          ) AS D  PIVOT( SUM(val) FOR orderyear IN([2013],[2014],[2015]) ) AS P;

SELECT empid,   SUM(CASE WHEN orderyear = 2013 THEN val END) AS [2013],  SUM(CASE WHEN orderyear = 2014 THEN val END) AS [2014],  SUM(CASE WHEN orderyear = 2015 THEN val END) AS [2015]FROM ( SELECT empid, YEAR(orderdate) AS orderyear, val       FROM Sales.OrderValues                          ) AS DGROUP BY empid;

UNPIVOT执行的是PIVOT相反但原理完全一致的操作。

然而，有两个问题比较困扰我：

1. 如何执行动态的PIVOT？比如，示例中的IN（）部分都是写死的，现实中显然这种固定值的情况不多。搜索了一圈下来，答案几乎全都是手动拼接IN后面的字符串。有一篇Blog值得一看。
2. 这个功能在现实中的意义？貌似这个没有标准答案。无论如何，行列互转总是个噱头。

是为之记。
Alva Chien
2016.6.14