title: SQL Stuff Function with Variable in SQL Server
body: <p>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. </p>
  
  <p>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?</p>
  
  <p>See code example as below:
  <strong>Working Code:</strong></p>
  
  <pre><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 
  </code></pre>
  
  <p><strong>Not Working Code as below</strong></p>
  
  <pre><code>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)
  </code></pre>
  
  <p><em>Not Working code returns the sql query itself rather than result.</em></p>
  
  <p>Any Idea or suggestions?</p>
  
  <p>Cheers
  Hardeep</p>