如何通过SQL的PIVOT实现行转列，反转表中追加的行数据？

你的报错通常是因为PIVOT语法使用不规范——最常见的问题是子查询未指定别名，或者PIVOT子句的格式有误。下面直接给你可行的解决方案，分两种场景处理：

场景1：已知所有需要转成列的ID值 #

假设你的源表名为SensorData，结构和示例数据如下：

CREATE TABLE SensorData (
    [Timestamp] DATETIME,
    ID VARCHAR(10),
    Value DECIMAL(10,2)
);

INSERT INTO SensorData VALUES
('2024-01-01 08:00:00', 'ID01', 25.5),
('2024-01-01 08:00:00', 'ID02', 30.2),
('2024-01-01 09:00:00', 'ID01', 26.1),
('2024-01-01 09:00:00', 'ID02', 31.0);

正确的PIVOT查询写法如下（这里用MAX作为聚合函数，因为每个Timestamp+ID组合只会有一个Value，聚合不影响结果）：

SELECT [Timestamp], [ID01], [ID02]
FROM (
    SELECT [Timestamp], ID, Value
    FROM SensorData
) AS SourceData  -- 必须给子查询指定别名，这是常见报错点
PIVOT (
    MAX(Value)  -- PIVOT必须配合聚合函数，根据实际情况选MAX/SUM等
    FOR ID IN ([ID01], [ID02])  -- 需要转成列的ID，必须用方括号包裹（如果ID含特殊字符或与关键字冲突）
) AS PivotTable;

执行后会得到期望的结果：

场景2：ID值不固定，需要动态生成列 #

如果你的ID是动态新增的，无法提前写出所有列名，可以用动态SQL实现：

DECLARE @Columns NVARCHAR(MAX), @SQL NVARCHAR(MAX);

-- 拼接所有需要转成列的ID
SELECT @Columns = STRING_AGG(QUOTENAME(ID), ', ')
FROM (SELECT DISTINCT ID FROM SensorData) AS UniqueIDs;

-- 构建动态PIVOT查询
SET @SQL = N'
SELECT [Timestamp], ' + @Columns + '
FROM (
    SELECT [Timestamp], ID, Value
    FROM SensorData
) AS SourceData
PIVOT (
    MAX(Value)
    FOR ID IN (' + @Columns + ')
) AS PivotTable;';

-- 执行动态SQL
EXEC sp_executesql @SQL;

常见报错原因排查 #

• 忘记给PIVOT的源子查询加别名：PIVOT要求前面的子查询必须有别名，否则会报语法错误
• 聚合函数使用错误：PIVOT必须配合聚合函数（MAX/SUM/AVG等），不能直接用字段
• FOR子句的列名格式错误：ID值必须用QUOTENAME()或方括号包裹，避免与SQL关键字冲突

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