SQL Server SQL服务器：使用PIVOT时"标识符无效"

q1qsirdb 于 2023-02-03 发布在其他

关注(0)|答案(1)|浏览(284)

我有一个临时表#temproles，包含如下数据：
| 识别号|科室|角色代码|角色名称|名称|
| - ------|- ------|- ------|- ------|- ------|
| 小行星123456|小行星65576|1个|中电|法兰克|
| 小行星123456|小行星65576|第二章|SUH|苏珊|
| 小行星234567|小行星6557|第二章|SUH|苏珊|
| 小行星234567|小行星65576|十三|CLH|艾莉森|
我正在尝试使用PIVOT返回如下数据集
| 识别号|科室|中电|SUH|CLH|
| - ------|- ------|- ------|- ------|- ------|
| 小行星123456|小行星65576|法兰克|苏珊|零|
| 小行星234567|小行星6557|零|苏珊|艾莉森|
这是我目前掌握的情况：

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

这将返回以下错误：
消息102，级别15，状态1，第9行
“BDA”附近的语法不正确。
我以前使用的是EXEC @DynamicPivotQuery，但得到了一个错误，其中：
N 'SELECT ID、部门等的名称不是有效的标识符。
我的理解是这是不正确的，所以我把它改成了EXEC sp_executesql @DynamicPivotQuery。任何帮助都将不胜感激！
仅供参考

SELECT STUFF((SELECT DISTINCT ',' + QUOTENAME(cdservicingrolecode, '''')
FROM #JLtemproles
GROUP BY uniqpolicy, cdservicingrolecode
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

退货

'BDA','BDP','CMW','BRC','CXA','CXH','BRP','SUH','CXP','BDH','CLP','BDL','PLH','CLH','SUP','PLP','BIP','BDS','CMP','CLA'

sql-server

来源：https://stackoverflow.com/questions/75291795/sql-server-not-valid-identifier-when-using-pivot

1条答案

按热度按时间

5ssjco0h1#

我能够修复查询。PIVOT需要一个聚合函数，所以ISNULL()将无法工作。我将其替换为MIN()。角色名称也需要用括号括起来，而不是用引号括起来。以下是代码沿着我的临时表测试。

DROP TABLE IF EXISTS #temproles
CREATE TABLE #temproles (ID INT, Dept INT, RoleCode INT, RoleName VARCHAR(3), NameOf VARCHAR(10))

INSERT INTO #temproles
SELECT 123456, 66576, 1, 'CLP', 'Frank'
UNION ALL SELECT 123456, 66576, 2, 'SUH', 'Susan'
UNION ALL SELECT 234567, 65578, 2, 'SUH', 'Susan'
UNION ALL SELECT 234567, 65576, 13, 'CLH', 'Alison'

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

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

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

EXEC sp_executesql @DynamicPivotQuery

赞(0）回复(0）举报 2023-02-03

我来回答

SQL Server SQL服务器：使用PIVOT时"标识符无效"

1条答案

相关问题

热门标签

最新问答