title: Dynamically add columns to temp table based on rows in another temp table
body: <p>I am trying to build a temp table with a dynamic number of columns based on the number of rows from another temp table. Say I have 89 rows in #table1, in #table2 I would like to use the row count and take that corresponding row value as the column name. I've been fiddling with this for a while but I keep getting errors. Here's my query which will be turned into a proc later.</p>
  
  <p>My table looks like this (all columns are varchar with null allowed in case the imported date has no data for that CVE Number - CVEId relates to FK constraint CVEID on CVENumber table):</p>
  
  <pre><code>CVEId     D20160901     D20160902     D20160903     D20160904     D20160905
  1         6182          6473          5879          NULL          NULL
  2         72862         76583         NULL          NULL          74772
  
  CVENumber Table:
  CVEID     CVENumber
  1         CVE-781-2016
  2         CVE-006-2016
  </code></pre>
  
  <p>What I'm hoping for is to get the date of the column or perhaps use an injected date as the first row - run a query against this data where I can specify 09-01-2016 TO 09-03-2016. And return all rows from the table with the CVENumber referred to in the CVENumber table. What I want my result to look like:</p>
  
  <pre><code>CVE Number     09-01-2016     09-02-2016     09-03-2016
  CVE-781-2016         6182           6473           8579
  CVE-006-2016        72682          76583              0
  </code></pre>
  
  <p>I hope this clarifies what I am trying to do. </p>
  
  <p>My current query using STUFF() which takes the rows from #FixedDates and turns those into columns. I want to take those columns returned to @cols to be added as columns to #query_results</p>
  
  <pre><code>Set nocount on
  
  Insert #tmp
  EXEC sp_columns @table_name = N'CVECountsByDate'
  
  -- Using collate to force the DB to only look at Uppercase values
  DECLARE @cols varchar(max), @query varchar(max), @cols2 varchar(MAX)
  
  INSERT #FixedDays
  SELECT Replace(COLUMN_NAME, 'D' collate Latin1_General_CS_AS, '' collate Latin1_General_CS_AS) from #Tmp 
  WHERE COLUMN_NAME LIKE 'D%' collate Latin1_General_CS_AS OR COLUMN_NAME = 'CVEId' ORDER BY COLUMN_NAME DESC
  
  
  SET @cols = STUFF((SELECT ',' + QUOTENAME(QT.COLUMN_NAME) + ' varchar(100)'
                   FROM #FixedDays QT
                   GROUP BY QT.COLUMN_NAME
                   ORDER BY QT.COLUMN_NAME
                   FOR XML PATH(''), TYPE
                   ).value('.', 'VARCHAR(MAX)')
                   ,1,1,'')
  
  SET @cols2 = N'CREATE TABLE #query_results (' + @cols + ') '
  --EXEC(@cols2)
  SELECT @cols2
  
  DROP TABLE #FixedDays
  DROP TABLE #Tmp
  </code></pre>