SQL SERVER 'Not valid identifier' when using PIVOT-CodePudding

I have a temp table "#temproles" with data structured like this:

ID	Dept	RoleCode	RoleName	NameOf
123456	65576	1	CLP	Frank
123456	65576	2	SUH	Susan
234567	65578	2	SUH	Susan
234567	65576	13	CLH	Alison

I am trying to use PIVOT to return a data like this

ID	Dept	CLP	SUH	CLH
123456	65576	Frank	Susan	NULL
234567	65578	NULL	Susan	Alison

This is what I have so far:

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

This returns the following error:

Msg 102, Level 15, State 1, Line 9 Incorrect syntax near 'BDA'.

I previously was using EXEC @DynamicPivotQuery but received an error where the:

name of N'SELECT id, dept, etc.. was not a valid identifier.

My understanding is this was not correct so I changed it to EXEC sp_executesql @DynamicPivotQuery. Any help would be greatly appreciated!

For reference

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

Returns

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

CodePudding user response：

I was able to fix the query. The PIVOT needs an aggregate function, so ISNULL() will not work. I replaced it with MIN(). The role names also need to be enclosed in brackets rather than quotes. Here is the code along with my temp table test.

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