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