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
– Fred, Commented Oct 16, 2013 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
– Damien_The_Unbeliever, Commented Oct 16, 2013 at 10:20
check this Dynamic pivot query Or Dynamic pivot multiple columns — bvr
– bvr, Commented Oct 16, 2013 at 10:45

Fred · Accepted Answer · 2013-10-16 10:41:56Z

2

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

answered Oct 16, 2013 at 10:41

Fred

5,8684 gold badges47 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Stuff Function with Variable in SQL Server

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related