行转列

在MSSQL Server中，可以使用PIVOT关键字来执行行转列操作。PIVOT操作将一个表中的行转换为列。以下是一个示例查询，它将Sales表中的每个月的销售金额转换为单个行：

 

SELECT ProductName, [1] AS Jan, [2] AS Feb, [3] AS Mar, [4] AS Apr, [5] AS May, [6] AS Jun, [7] AS Jul, [8] AS Aug, [9] AS Sep, [10] AS Oct, [11] AS Nov, [12] AS Dec
FROM 
(SELECT ProductName, Month(OrderDate) AS SaleMonth, TotalDue FROM Sales) AS s
PIVOT 
(
SUM(TotalDue)
FOR SaleMonth IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS pvt;

这个查询会将Sales表中每个月的销售总额转换为单个行，每行包含产品名称和12个月的销售总额。在这个示例中，使用PIVOT和FOR的目的是将Sales表中的SaleMonth列数据转换为列名。最后一步是使用PIVOT子句中的SUM函数来将Sales表中每个月的销售总额作为值聚合到每个列中。