title: How to export dynamic SQL Server pivot table to excel
body: <p>I'm working on a webapp that displays data from a Microsoft SQL Server dynamic pivot table.</p>
  
  <p>Normally I'd try and figure out a way to do the dynamic pivot in c#, but in this case the pivot has to be a SQL Server stored procedure because other apps also need access to the pivot table. </p>
  
  <p>Here's the SQL:</p>
  
  <pre><code>DECLARE @DynamicPivot AS NVARCHAR(MAX)
  DECLARE @ColumnName AS NVARCHAR(MAX)
  
  SELECT 
      @ColumnName = ISNULL(@ColumnName + ',', '') 
                    + QUOTENAME(xml_tag_name)
  FROM 
      (SELECT DISTINCT xml_tag_name FROM DataEntries) AS TagValues
  
  SET @DynamicPivot =
  N'SELECT DISTINCT capture_id, ' + @ColumnName + '
  FROM DataEntries
  PIVOT(MAX(xml_tag_value) 
  FOR xml_tag_name IN (' + @ColumnName + ')) AS PVTTable'
  
  EXEC sp_executesql @DynamicPivot
  </code></pre>
  
  <p>All the articles I've gone through deal with normal export to Excel or static pivots. eg: <a href="https://stackoverflow.com/questions/23028604/export-table-from-sql-server-to-excel-2007-using-c-sharp">Export Table from SQL Server to Excel 2007 using C#</a>. </p>
  
  <p>How do I go about exporting this dynamic pivot to Excel?</p>