T-SQL使用Pivot转换天气关系表为城镇温压报表结果异常如何解决

• 聚合对象错误：PIVOT子句中使用了max(Name)作为聚合函数，直接对城镇名称聚合，最终列输出自然是城镇名而非气象数值，你需要聚合的是Temp和Pressure指标值。
• 分组维度错误：源查询的SELECT列表中包含了Temp字段，PIVOT会自动按SELECT列表里除了聚合字段、转置字段之外的所有字段分组，也就是按Created + Temp分组，同一时间戳只要温度不同就会拆成多行，出现数据分散的问题。
• 指标扩展逻辑缺失：你需要同时输出温度、气压两个指标，仅按城镇名转置只能得到单指标结果，需要先把两个指标拆成行记录，再拼接城镇名+指标名作为转置的列名。
• 温度计算错误：T-SQL中整数除法9/5结果为1，导致开尔文转华氏度的计算结果错误，需要改为浮点运算9.0/5；另外开尔文转摄氏度的基准值为273.15而非273.35，可根据实际业务需求调整。

修正后的完整动态查询代码如下，可直接输出符合要求的报表格式：

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

-- 生成所有转置列：格式为「[城镇名 Temp]」「[城镇名 Pressure]」
SELECT @cols = STUFF((
    SELECT DISTINCT ',' + QUOTENAME([Name] + ' Temp') + ',' + QUOTENAME([Name] + ' Pressure')
    FROM WeatherResponse
    ORDER BY 1
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SET @query = '
SELECT Created AS [Date/Time], ' + @cols + '
FROM (
    SELECT 
        r.Created,
        -- 拼接转置用的列名
        col_name = r.Name + v.metric_name,
        -- 对应指标的数值
        metric_value = v.metric_value
    FROM WeatherResponse r 
    INNER JOIN Mains w ON w.WeatherResponseId = r.WeatherResponseId
    -- 把温度、气压两个指标拆为两条行记录
    CROSS APPLY (
        VALUES 
            ('' Temp'', ((w.Temp-273.15) * (9.0/5)) + 32), -- 开尔文转华氏度，修正整数除法问题
            ('' Pressure'', CAST(w.Pressure AS FLOAT))
    ) v(metric_name, metric_value)
) x
PIVOT (
    MAX(metric_value) -- 聚合指标数值
    FOR col_name IN (' + @cols + ')
) p
ORDER BY Created
'

EXEC sp_executesql @query

代码逻辑说明：通过CROSS APPLY + VALUES语法将单条数据里的温度、气压两个指标拆为两条行记录，同时拼接得到「城镇名+指标名」的转位列名，最终通过PIVOT转置后即可得到每个时间戳一行、每个城镇对应两列指标的报表格式。

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