SQLServer中几种行列转换的方式
--查询SalesOrder中每年每个月各个customer的产生的订单总数量
1、使用PIVOT
SELECT x.*
FROM ( SELECT YEAR(SalesOrderDate) [Year] ,
MONTH(SalesOrderDate) AS [Month] ,
CustomerCode ,
TotalQTY
FROM dbo.SalesOrder
) so
PIVOT (
SUM (so.TotalQTY)
FOR [Month] IN ( [1], [2], [3], [4], [5],[6], [7], [8], [9],[10], [11], [12] )
)x
ORDER BY x.[Year] ,
x.CustomerCode
2、case when
SELECT YEAR(SalesOrderDate) AS Year,
CustomerCode,
SUM(CASE MONTH(SalesOrderDate)WHEN 1 THEN TotalQTY END) AS '1',
SUM(CASE MONTH(SalesOrderDate)WHEN 2 THEN TotalQTY END) AS '2',
SUM(CASE MONTH(SalesOrderDate)WHEN 3 THEN TotalQTY END) AS '3',
SUM(CASE MONTH(SalesOrderDate)WHEN 4 THEN TotalQTY END) AS '4',
SUM(CASE MONTH(SalesOrderDate)WHEN 5 THEN TotalQTY END) AS '5',
SUM(CASE MONTH(SalesOrderDate)WHEN 6 THEN TotalQTY END) AS '6',
SUM(CASE MONTH(SalesOrderDate)WHEN 7 THEN TotalQTY END) AS '7',
SUM(CASE MONTH(SalesOrderDate)WHEN 8 THEN TotalQTY END) AS '8',
SUM(CASE MONTH(SalesOrderDate)WHEN 9 THEN TotalQTY END) AS '9',
SUM(CASE MONTH(SalesOrderDate)WHEN 10 THEN TotalQTY END) AS '10',
SUM(CASE MONTH(SalesOrderDate)WHEN 11 THEN TotalQTY END) AS '11',
SUM(CASE MONTH(SalesOrderDate)WHEN 12 THEN TotalQTY END) AS '12'
FROM dbo.SalesOrder s
GROUP BY YEAR(s.SalesOrderDate),
CustomerCode
ORDER BY YEAR(SalesOrderDate),CustomerCode
3、动态条件
DECLARE @PivotColHeader VARCHAR(MAX)
SELECT @PivotColHeader =
COALESCE(@PivotColHeader + ',[' + cast(MONTH(SalesOrderDate) as varchar) + ']', '[' + cast(MONTH(SalesOrderDate) as varchar) + ']')
SELECT @PivotColHeader =
COALESCE(@PivotColHeader + ',[' + cast(MONTH(SalesOrderDate) as varchar) + ']', '[' + cast(MONTH(SalesOrderDate) as varchar) + ']')
--示例中Name转换为varchar或char类型，注意：在CAST 和CONVERT 中使用varchar 时，显示n的默认值为30
FROM SalesOrder
GROUP BY MONTH(SalesOrderDate);
DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
SELECT x.*
FROM ( SELECT YEAR(SalesOrderDate) [Year] ,
MONTH(SalesOrderDate) AS [Month] ,
CustomerCode ,
TotalQTY
FROM dbo.SalesOrder
) so
PIVOT (
SUM (so.TotalQTY)
FOR [Month] IN ('+@PivotColHeader+')
)x
ORDER BY x.[Year] ,
x.CustomerCode'
EXECUTE sp_executesql @PivotTableSQL
FROM SalesOrder
GROUP BY MONTH(SalesOrderDate);
DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
SELECT x.*
FROM ( SELECT YEAR(SalesOrderDate) [Year] ,
MONTH(SalesOrderDate) AS [Month] ,
CustomerCode ,
TotalQTY
FROM dbo.SalesOrder
) so
PIVOT (
SUM (so.TotalQTY)
FOR [Month] IN ('+@PivotColHeader+')
)x
ORDER BY x.[Year] ,
x.CustomerCode'
EXECUTE sp_executesql @PivotTableSQL
