带前缀的SQL数据透视列

Question

SQL新手，很难完全理解pivot子句。我有四个字段(州、季节、雨、雪(，并试图进行调整，使我有5个字段(状态、summer_rain、summer_snow、winter_rain、winter_snow(。我不知道如何调整两个字段，以便在它们前面加上另一个字段(如果有意义的话(。转载如下。

我现在拥有的

之后我在做什么

我的代码(在pivot子句中聚合雨雪时收到错误(：

DECLARE @myTable AS TABLE([state] VARCHAR(20), [season] VARCHAR(20), [rain] int, [snow] int)
INSERT INTO @myTable VALUES ('AL', 'summer', 1, 1)
INSERT INTO @myTable VALUES ('AK', 'summer', 3, 3)
INSERT INTO @myTable VALUES ('AZ', 'summer', 0, 1)
INSERT INTO @myTable VALUES ('AL', 'winter', 5, 4)
INSERT INTO @myTable VALUES ('AK', 'winter', 2, 2)
INSERT INTO @myTable VALUES ('AZ', 'winter', 1, 1)
INSERT INTO @myTable VALUES ('AL', 'summer', 6, 4)
INSERT INTO @myTable VALUES ('AK', 'summer', 3, 0)
INSERT INTO @myTable VALUES ('AZ', 'summer', 5, 1)
SELECT [state], [year], [month], [day]
FROM
(
SELECT * FROM @myTable
) t
PIVOT
(
sum([rain]), sum([snow]) FOR [season] IN ([summer], [winter]) 
) AS pvt

Answer 1

PIVOTS很棒，但Conditional Aggregations提供了更多的灵活性，而且通常更具性能。

PIVOT

Select *
From  (
SELECT State 
,B.*
FROM  @myTable
Cross Apply (values (concat(season,'_rain'),rain)
,(concat(season,'_snow'),snow)
) B(Item,Value)
) src
Pivot ( sum(value) for Item in ([summer_rain],[summer_snow],[winter_rain],[winter_snow]) ) pvt

条件聚合

Select State
,[summer_rain] = sum(case when season='summer' then rain end)
,[summer_snow] = sum(case when season='summer' then snow end)
,[winter_rain] = sum(case when season='winter' then rain end)
,[winter_snow] = sum(case when season='winter' then snow end)
From  @myTable
Group By State