在SQL Server中使用Pivot将行转换为列

Question

我有一个包含数据的表格：

date            Plant          Weight
2017-11-11  Gent        26
2017-11-11  Ang         25
2017-11-11  Ger         24
2017-11-11  ISS         23
2017-11-10  Gent        26
2017-11-10  Ang         25
2017-11-11  Ger         24
2017-11-11  ISS         23
2017-11-11  Gent        12
2017-11-11  Ang         13
2017-11-10  Gent        100

我希望它以数据透视表的形式出现，如下所示：

datum           Ang Gent  Ger  ISS
2017-11-10      25  126   0    0
2017-11-11      38   38  48   46

谢谢你的帮助。

Answer 1

试试这个：

SELECT *
FROM myTable
PIVOT
(
   MAX(Weight) FOR [Plant] IN ([Ang], [Gent], [Ger], [ISS])
) PVT;

Answer 2

首先，如果不想使用Pivot运算符，可以使用条件case表达式条件

SELECT DATE [datum],
          SUM(CASE(PLANT) WHEN 'Ang' THEN Weight ELSE NULL END) [Ang],
          SUM(CASE(PLANT) WHEN 'Gent' THEN Weight ELSE NULL END) [Gent],
          SUM(CASE(PLANT) WHEN 'Ger' THEN Weight ELSE 0 END) [Ger],
          SUM(CASE(PLANT) WHEN 'ISS' THEN Weight ELSE 0 END) [ISS]
FROM <table> GROUP BY [DATE]

另一种是动态pivot方式，如果您对条件case表达式不满意

DECLARE @Col NVARCHAR(MAX)
DECLARE @Query NVARCHAR(MAX)

SET @Col = STUFF((SELECT DISTINCT ','+QUOTENAME(PLANT) FROM <table> FOR XML PATH('')),1,1, '')

SET @Query = N'SELECT * FROM <table> 
                    PIVOT
                    (
                       sum(Weight) FOR [Plant] IN ('+@Col+')
                    ) PVT'

execute sp_executesql @Query

结果：

datum           Ang Gent  Ger  ISS
2017-11-10      25  126   0    0
2017-11-11      38   38  48   46

Answer 3

select [datum],[Gent],[Ang],[ISS],[Ger],[Gent]+[Angouleme]+[ISS]+[Gerona] as [TOTAAL] FROM (select plant,datum,sum(weight) as weight FROM [MEPDARPD].[dbo].[Table1]  group by plant,datum)t
PIVOT
(
sum(Weight) 
FOR [Plant] IN ([Gent],[Ang],[ISS],[Ger])
) AS p

查询下面的行和列中的合计

select datum,
sum(case(plant) WHEN 'Ang' then weight else 0 END) as [Ang],
sum(case(plant) WHEN 'Gent' then weight else 0 END) as [Gent],
sum(case(plant) WHEN 'ISS' then weight else 0 END) as [ISS],
sum(case(plant) WHEN 'Ger' then weight else 0 END) as [Ger],
SUM(weight) as total
from MEPDARPD.dbo.rous group by datum
with rollup

这个查询解决了我的问题。谢谢你的帮助。