Could not execute properly the Dynamic Pivot?

Question

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".

Answer 1

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