SQL带前缀列透视问题：如何同时透视rain与snow字段

你的代码存在两个核心问题：

• SQL Server的PIVOT语法一次只能聚合单个列，无法同时指定sum([rain]), sum([snow])
• SELECT语句里的[year], [month], [day]在原表中不存在，属于无效字段

针对你需要的「按state聚合，生成带季节前缀的rain/snow字段」需求，这里提供两种实用解法：

方法一：条件聚合（新手友好，逻辑直观） #

这种方法无需复杂的PIVOT嵌套，直接用CASE WHEN配合聚合函数实现，更容易理解和调试：

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],
    SUM(CASE WHEN [season] = 'summer' THEN [rain] ELSE 0 END) AS summer_rain,
    SUM(CASE WHEN [season] = 'summer' THEN [snow] ELSE 0 END) AS summer_snow,
    SUM(CASE WHEN [season] = 'winter' THEN [rain] ELSE 0 END) AS winter_rain,
    SUM(CASE WHEN [season] = 'winter' THEN [snow] ELSE 0 END) AS winter_snow
FROM @myTable
GROUP BY [state]

运行后会直接生成你需要的5列结构，按state分组汇总对应季节的雨雪数据。

方法二：UNPIVOT + PIVOT组合（适合复杂多列透视场景） #

如果后续需要处理更多类似的度量列，可以先把rain和snow转成行，再将季节与度量名拼接成新列名，最后完成透视：

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],
    [summer_rain],
    [summer_snow],
    [winter_rain],
    [winter_snow]
FROM (
    -- 先把rain和snow转成行，用metric列标记是rain还是snow
    SELECT
        [state],
        CONCAT([season], '_', [metric]) AS season_metric,
        [value]
    FROM @myTable
    UNPIVOT (
        [value] FOR [metric] IN ([rain], [snow])
    ) AS unpvt
) AS t
PIVOT (
    SUM([value]) FOR [season_metric] IN (
        [summer_rain], [summer_snow], [winter_rain], [winter_snow]
    )
) AS pvt

这个方法的核心是先将多列度量转成行结构，再组合季节与度量名生成新的标识列，最后一次性透视出所有目标字段。

内容的提问来源于stack exchange，提问作者intransigent_rocker