SQL Server使用PIVOT时出现'无效标识符'错误求助

问题背景 #

现有临时表#temproles，结构及数据如下：

尝试用动态PIVOT转换为宽表时，执行代码提示'BDA'附近有语法错误；最初使用EXEC @DynamicPivotQuery报错N'SELECT id, dept...不是有效标识符'，换成EXEC sp_executesql @DynamicPivotQuery后仍报错。要求查询返回带单引号的角色编码列表，原代码如下：

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

SELECT 
    @ColumnName = STUFF((SELECT DISTINCT ',' + QUOTENAME(rolename, '''')
FROM #temproles
GROUP BY id, rolename
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

SET @DynamicPivotQuery =
    N'SELECT id, dept,' + @ColumnName + N' FROM 
    (
        SELECT id, dept, rolename, nameOf
        FROM #temproles
    ) x
    PIVOT 
    (
        ISNULL(nameOf, '''')
        FOR rolename IN (' + @ColumnName + N')
    ) p'

EXEC sp_executesql @DynamicPivotQuery

错误原因 #

1. 列名格式不符合PIVOT语法：QUOTENAME(rolename, '''')生成的是'CLP'这类带单引号的字符串，而PIVOT的FOR ... IN()子句要求列名用方括号包裹（如[CLP]），单引号会被SQL解析为字符串常量，直接引发语法错误。
2. 冗余分组导致重复列名：GROUP BY id, rolename搭配DISTINCT会生成重复的角色名条目（同一角色对应不同ID时会被多次输出），导致@ColumnName中出现重复列名，触发语法冲突。
3. 聚合函数使用错误：PIVOT必须搭配聚合函数，原代码中直接用ISNULL(nameOf, '''')不符合语法要求。

修正后的代码 #

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

-- 生成带方括号的唯一角色名列，符合PIVOT语法要求
SELECT 
    @ColumnName = STUFF((SELECT DISTINCT ',' + QUOTENAME(rolename)
                         FROM #temproles
                         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

-- 构建动态PIVOT语句，同时实现输出列名带单引号的需求
SET @DynamicPivotQuery =
    N'SELECT id, dept, ' + 
    -- 将方括号列名替换为带单引号的别名
    REPLACE(REPLACE(@ColumnName, '[', ''''), ']', '''') + 
    N' FROM 
    (
        SELECT id, dept, rolename, nameOf
        FROM #temproles
    ) x
    PIVOT 
    (
        MAX(nameOf) -- 用MAX作为聚合函数，因每个id+dept+rolename对应唯一nameOf，不影响结果
        FOR rolename IN (' + @ColumnName + N')
    ) p'

EXEC sp_executesql @DynamicPivotQuery

额外优化（空值显示为空字符串） #

如果需要将NULL值显示为''，可修改外层SELECT语句：

SET @DynamicPivotQuery =
    N'SELECT id, dept, ' + 
    -- 对每个列添加ISNULL处理空值
    REPLACE(REPLACE(@ColumnName, '[', 'ISNULL(''['), ']', '''], '''') + '''' +
    N' FROM 
    (
        SELECT id, dept, rolename, nameOf
        FROM #temproles
    ) x
    PIVOT 
    (
        MAX(nameOf)
        FOR rolename IN (' + @ColumnName + N')
    ) p'

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