SQL Stuff Function with Variable in SQL Server

Question

I am trying to generate an Pivot table with SQL (SQL Server 2008). To get the column list I am using stuff function which works great if we use it with SQL.

Although due to dynamic nature of Pivot structure (selection by user) I want to make column name set as a variable. I can catch correct SQL Stuff syntax but not able to execute it. Any Idea?

See code example as below: Working Code:

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Station) 
                    from #ResultCrosstab
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
Select @cols 

Not Working Code as below

Declare @ColumnName varchar(100)
set @ColumnName='Station'
DECLARE @cols1 AS NVARCHAR(MAX)
DECLARE @queryCol AS NVARCHAR(MAX)

set @queryCol='STUFF((SELECT distinct '','' + QUOTENAME(' + @ColumnName + ') 
                    from #ResultCrosstab
            FOR XML PATH(''), TYPE
            ).value(''.'', ''NVARCHAR(MAX)'') 
        ,1,1,'''')'

Select @queryCol
Select @cols1=(@queryCol)

Not Working code returns the sql query itself rather than result.

Any Idea or suggestions?

Cheers Hardeep

Solution

Execute the query rather than select it. Select @queryCol will return the value of @queryCol

Select @cols1=(@queryCol) will put the value of @queryCol into @cols1

You will need to EXEC SP_EXECUTESQL(@queryCol) or EXEC(@queryCol) to execute the actual query

Comments

[Damien_The_Unbeliever]

I don't know why people frequently refer to this way of constructing a comma separated string of values as the/a STUFF method. Are you aware that all that STUFF is doing is trimming the leading , off of the text that FOR XML PATH is generating.

[Fred]

I cant see where you are executing the query. Am I missing something?