title: SQL Server 2012: Dynamic crosstab in stored procedure
body: <p>Im trying to use a stored procedure to create a pivot table between two declared variables @rvar (as rowvariable) and @cvar (as columnvariable). The point is to call the stored procedure from VBA using these two as dynamic input when executing the stored procedure. </p>
  
  <p>My code has three parts: </p>
  
  <ol>
  <li>creating test-data </li>
  <li>declaring locals</li>
  <li>finding names of columns in crosstab an storing in new local @sql1</li>
  <li>executing crosstable with the pivotfunction using the names stored in @sql1.</li>
  </ol>
  
  <p><strong><code>My problem:</code></strong> The code below works but I would like to make it dynamic regarding the variable defining the column structure - currently set to "q10_1_resp" - so that I only have to declare the local @cvar and use that in part 3 (like in part 4). I have succeeded in making part 3 into a sql-string with subsequent execution but then the column names stored in @sql1 cannot be used in the code in part 4 (I guess it is a scope thing).</p>
  
  <pre><code>--Part 1
  
  create table [user].[test]
      (rowvar nvarchar(max),
      q10_1_resp int,
      q10_2_resp int)
      GO 
  
  INSERT [user].[test]
          VALUES ('PH',1,2),
              ('PH',2,3),
              ('EA',1,5),
              ('EA',5,4),
              ('PH',3,4),
              ('PH',6,6),
              ('EA',4,1),
              ('PH',5,3),
              ('PH',2,1)
          GO
  
  -- Part 2
  declare @rvar as nvarchar(max) = 'rowvar'
  declare @cvar as nvarchar(max) = 'q10_1_resp' --this input should be dynamic as well
  declare @sql1 as nvarchar(max)= ''
  declare @sql2 as nvarchar(max)= ''
  
  -- Part 3
  select @sql1 = @sql1 + [a].[col] + char(44)
      from
          (select distinct QUOTENAME(q10_1_resp) as [col]
          from [user].[test]
          group by q10_1_resp) as a
  
  SET @sql1 = left(@sql1, len(@sql1) - 1)
  
  -- Part 4
  SET @sql2 = 'select ' +
      + @rvar + ','
      + @sql1 
      + ' from (Select '
      + @rvar + ', ' 
      + @cvar
      + ' from [user].[test]) sq pivot(count(' 
      + @cvar 
      + ') for '
      + @cvar + ' IN (' 
      + @sql1 
      + ')) as pt'
  
  exec sp_executesql @sql2
  </code></pre>