title: MS SQL: return HTML from SQL PIVOT RESULTS with dynamic name/number of columns
body: <p>I am using MS-SQL 2008 and have a dynamic SQL query in a Stored Proc. that generates results using PIVOT.  The last columns of the pivot table can change both in the number of columns and the names based on user provided parameters.  </p>
  
  <p>The user is able to select/provide the number of months prior to the current month that they want to see results for. </p>
  
  <pre>
  Example Results: (User selects 2 months)
  Product  DataDescript          2015-06    2015-05
  -------  --------------------  -------    -------
  Apples   AVG_Days_To_Ripen      15          18
  Peaches  AVG_Days_To_Shipping   03          08
  
  Example Results: (User selects 3 months)
  Product  DataDescript          2015-06    2015-05    2015-04
  -------  --------------------  -------    -------    -------
  Apples   AVG_Days_To_Ripen      15          18        20
  Peaches  AVG_Days_To_Shipping   03          08        07
  </pre>
  
  <p>I know how to return HTML (XML) results from a static dataset, but I am having a hard time thinking through how I would do it with a data set that is dynamic like this. </p>
  
  <p><b>Typically When I'm doing An HTML table based off of a static set of columns 
  I usually do something similar to the following:  </b></p>
  
  <pre>
  SET @vMyHTMLTABLE = (
  
      '&lt table>
          &lt TR>
          &lt TH> WIDTH ="10%">Column1_Header &lt /TH>                                                    
          &lt TH> WIDTH ="10%">Column2_Header &lt /TH>
          &lt TH> WIDTH ="10%">Column3_HEader &lt /TH>
          &lt /TR>' 
      +   CAST((SELECT
                  CASE WHEN (ROW_NUMBER() OVER(order by emt.Email_RUID ASC ) %2) = 0 THEN 'evn' ELSE 'odd' END as [td/@class],'Col1ID' as [td/@id] ,(emt.Column1)  as 'td','',
                  CASE WHEN (ROW_NUMBER() OVER(order by emt.Email_RUID ASC ) %2) = 0 THEN 'evn' ELSE 'odd' END as [td/@class],'Col2ID' as [td/@id] ,(emt.Column2) as 'td','',
                  CASE WHEN (ROW_NUMBER() OVER(order by emt.Email_RUID ASC ) %2) = 0 THEN 'evn' ELSE 'odd' END as [td/@class],'Col3ID' as [td/@id] ,(emt.Column3) as 'td'
              FROM @t_MyEmailTable emt
              WHERE 
              ORDER BY emt.Email_RUID ASC
              FOR XML PATH('tr'), ELEMENTS ) AS nvarchar(max))
      + '&lt /table>'
  
  )
  </pre>
  
  <p><b>To Generate my Dynamic Pivot table I'm doing something like this:</b></p>
  
  <pre>
  DECLARE @DynamicPivotQuery NVARCHAR(MAX)
  DECLARE @ColumnName        NVARCHAR(MAX)
  
  --Get my list of dynamic pivot Columns 
      SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(LEFT(CAST(MyMonths.[YoMo] AS DATE),7))
          FROM (SELECT DISTINCT fr.[YoMo] FROM MyDB.dbo.FinalResults fr) AS MyMonths
  
  
  SET @DynamicPivotQuery = (
            N'SELECT 
                   ROW_NUMBER() OVER(order by DataAsOf ASC ) AS [RowNum]
                  ,CASE 
                      WHEN (ROW_NUMBER() OVER(order by DataAsOf ASC ) %2) = 0 THEN ''evn'' 
                      ELSE ''odd'' 
                  END                 AS [class]
                  ,LEFT(DataPoint,3)  AS [Act]
                  ,RIGHT(DataPoint,3) AS [Pct]
                  , *     
                  FROM (
                          select distinct          
                                fr.[Product]                    AS [Product]
                              , fr.[DataDescript]               AS [DataDescript] 
                              , LEFT(CAST(fr.[YoMo] AS DATE),7) AS [YoMo]     
                              , fr.[DataValue]                  AS [DataValue]    
  
                          FROM MyDB.dbo.FinalResults fr   
                          join MyDB.dbo.FinalGroups g
                          ON fr.GID = g.GID
                       ) AS D                                
                  PIVOT(
                          MAX(D.DataValue) FOR  D.YoMo IN(' + @ColumnName + ')
                       ) AS P 
  
                  order by Product, DataDescript
                  ') -- END: SET @DynamicPivotQuery
  
          EXEC tempdb.dbo.sp_executesql @DynamicPivotQuery --TempDB because source DB is in SQL 2000 Compat mode. 
  </pre>
  
  <p>Any thoughts or insight would be greatly appreciated!</p>