SQL Server中如何按月透视温度数据？现有PIVOT代码报错求助

嘿，我帮你排查下这个PIVOT的问题，你的代码里有几个语法和逻辑上的小问题，我一步步给你修正：

原代码的错误点 #

• 内层子查询逻辑违规：你group by Ville但直接select Temperature，这不符合SQL聚合规则（非聚合列必须出现在GROUP BY子句中），而且这里根本不需要提前分组，应该先提取月份再做聚合统计。
• PIVOT里的CAST语法错误：括号位置写错了，正确格式是CAST(AVG(Temperature) AS DECIMAL(4,1))，而非cast(AVG(Temperature)) AS DECIMAL(4, 1)。
• 月份引用方式错误：DateDonnees是日期类型，不能直接在IN里写1 JAN这种格式，得先从日期中提取月份的数字或名称，再作为PIVOT的列标识。

正确的实现方案 #

我们需要先从DateDonnees中提取月份信息，再对每个城市的月平均温度进行PIVOT转换，下面给你两种常用写法：

写法1：用月份数字作为列名

SELECT Ville, [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 
        Ville,
        Temperature,
        MONTH(DateDonnees) AS MonthNum -- 提取月份数字
    FROM Meteo
    WHERE DateDonnees BETWEEN '2011-01-01' AND '2012-01-01'
) AS SourceData
PIVOT (
    CAST(AVG(Temperature) AS DECIMAL(4,1)) -- 计算月平均温度并格式化
    FOR MonthNum IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) AS PivotTable;

写法2：用月份名称作为列名

SELECT Ville, [January] AS Jan, [February] AS Feb, [March] AS Mar, [April] AS Apr, [May] AS May, [June] AS Jun,
       [July] AS Jul, [August] AS Aug, [September] AS Sep, [October] AS Oct, [November] AS Nov, [December] AS Dec
FROM (
    SELECT 
        Ville,
        Temperature,
        DATENAME(MONTH, DateDonnees) AS MonthName -- 提取月份名称
    FROM Meteo
    WHERE DateDonnees BETWEEN '2011-01-01' AND '2012-01-01'
) AS SourceData
PIVOT (
    CAST(AVG(Temperature) AS DECIMAL(4,1))
    FOR MonthName IN ([January], [February], [March], [April], [May], [June],
                     [July], [August], [September], [October], [November], [December])
) AS PivotTable;

补充说明 #

• 内层子查询先把日期转换成月份的数字/名称，让PIVOT能按月份维度分组统计。
• PIVOT中的AVG(Temperature)会自动结合Ville和提取的月份，完成每个城市的月平均温度计算。
• 最后给每个月份列设置简洁别名，让结果更易读。

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