最近看了看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的确简洁，方便！！哈哈！