Pivot PK Case

最近看了看Sql Server2008的Pivot 的行列转换

 

sql帮助文档的实例：

PIVOT

SELECT VendorID,[250], [251], [256], [257], [260]
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;

=====================

CASE   WHEN   THEN 

SELECT
VendorID ,
COUNT(CASE EmployeeID
WHEN '250' THEN PurchaseOrderID
END) AS '250' ,
COUNT(CASE EmployeeID
WHEN '251' THEN PurchaseOrderID
END) AS '251' ,
COUNT(CASE EmployeeID
WHEN '256' THEN PurchaseOrderID
END) AS '256' ,
COUNT(CASE EmployeeID
WHEN '257' THEN PurchaseOrderID
END) AS '257' ,
COUNT(CASE EmployeeID
WHEN '260' THEN PurchaseOrderID
END) AS '260'
FROM Purchasing.PurchaseOrderHeader
GROUP BY VendorID
ORDER BY VendorID

相比之下，Pivot的确简洁，方便！！哈哈！