MS SQL如何基于行值动态创建列实现行转列统计？

当然可以实现！这种需要根据列的动态唯一值来生成结果列的需求，在MS SQL里用动态SQL就能完美解决，正好适配你不确定Error列错误类型数量的场景。

先明确你的需求场景 #

原始数据表（简化后）：

ID | ID2 | Error
1  | 1   | A
1  | 2   | B
1  | 3   | A

期望按ID分组，统计每种Error类型的出现次数，动态生成对应列，结果如下：

ID | ErrorTypeA | ErrorTypeB
1  | 2          | 1

具体实现步骤与代码 #

下面分两种情况给出实现方案，分别适配不同版本的SQL Server：

方案1：适配SQL Server 2017及以上版本（使用STRING_AGG）

STRING_AGG是SQL Server 2017引入的字符串聚合函数，能更简洁地拼接动态列语句：

1. 先创建测试数据（模拟你的表）：

CREATE TABLE #TestData (
    ID INT,
    ID2 INT,
    Error VARCHAR(10)
);

INSERT INTO #TestData VALUES
(1, 1, 'A'),
(1, 2, 'B'),
(1, 3, 'A');

2. 编写动态SQL代码：

DECLARE @DynamicColumns NVARCHAR(MAX);
DECLARE @DynamicSQL NVARCHAR(MAX);

-- 生成每个Error类型对应的统计列语句
SELECT @DynamicColumns = STRING_AGG(
    CONCAT('[ErrorType', Error, '] = COUNT(CASE WHEN Error = ''', Error, ''' THEN 1 END)'),
    ', '
)
FROM (SELECT DISTINCT Error FROM #TestData) AS UniqueErrors;

-- 拼接完整的查询SQL
SET @DynamicSQL = CONCAT(
    'SELECT ID, ', @DynamicColumns, '
     FROM #TestData
     GROUP BY ID;'
);

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

方案2：适配SQL Server 2016及更早版本（使用FOR XML PATH）

如果你的SQL Server版本不支持STRING_AGG，可以用传统的FOR XML PATH方式拼接字符串：

DECLARE @DynamicColumns NVARCHAR(MAX);
DECLARE @DynamicSQL NVARCHAR(MAX);

-- 生成动态列语句（兼容旧版本）
SELECT @DynamicColumns = STUFF((
    SELECT ', [ErrorType' + Error + '] = COUNT(CASE WHEN Error = ''' + Error + ''' THEN 1 END)'
    FROM (SELECT DISTINCT Error FROM #TestData) AS UniqueErrors
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

-- 拼接并执行动态SQL
SET @DynamicSQL = CONCAT(
    'SELECT ID, ', @DynamicColumns, '
     FROM #TestData
     GROUP BY ID;'
);

EXEC sp_executesql @DynamicSQL;

关键注意事项 #

• 特殊字符处理：如果Error列的值包含单引号、空格或其他特殊字符，建议用QUOTENAME函数来生成更安全的列名，比如把[ErrorType' + Error + ']改成QUOTENAME('ErrorType' + Error)。
• SQL注入风险：动态SQL本身存在一定的注入风险，但如果只是从自身业务表中读取唯一值来生成语句，风险极低；如果涉及用户输入的参数，一定要做好参数化处理。
• 性能考虑：如果你的数据表非常大，建议先对Error列创建索引，提升获取唯一值和分组统计的效率。

执行上述代码后，就能得到你期望的动态列统计结果啦！

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