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

I cant see where you are executing the query. Am I missing something? — Fred, Oct 16 '13 at 10:20
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. — Damien_The_Unbeliever, Oct 16 '13 at 10:20
check this [**Dynamic pivot query**](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) Or [**Dynamic pivot multiple columns**](http://stackoverflow.com/questions/13072744/sql-server-pivot-with-multiple-x-axis-columns) — bvr, Oct 16 '13 at 10:45

score 2 · Accepted Answer · answered Oct 16 '13 at 10:41

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

SQL Stuff Function with Variable in SQL Server

1 Answers1