CODEWITHSUNDEEP
sql-servert-sqlpivotdynamic-pivot
priyanka.sarkar
priyanka.sarkar

Reputation: 26518

Could not execute properly the Dynamic Pivot?

I have a table

DECLARE @T TABLE(Val1 VARCHAR(20), Id INT, Val2 INT)
INSERT INTO @T VALUES
    ('X',1,2),
    ('Y',2,3)

If I want to run a PIVOT (STATIC), the query is 

SELECT ID, [X],[Y] from @T             
                PIVOT 
                (
                     MAX(Val2)
                     FOR Val1 IN ([X],[Y])
                ) AS X          
                ORDER BY 1

It works as expected. How can we perform the same in Dynamic PIVOT?

My attempt

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Val1) 
                    from @T                   
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ID, ' + @cols + N' from @T             
            PIVOT 
            (
                 MAX(Val2)
                 FOR Val1 IN (' + @cols + N')
            ) AS X          
            ORDER BY 1'

exec sp_executesql @query

Error:

Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@T".

Upvotes: 0

Views: 37

Answers (1)

James Z
James Z

Reputation: 12317

The dynamic SQL is executed in different context so it will not have the table variable. Use a temporary table (#T) instead.

Upvotes: 1

Related Questions