Dynamically add columns to temp table based on rows in another temp table

Question

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.

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):

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

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:

CVE Number     09-01-2016     09-02-2016     09-03-2016
CVE-781-2016         6182           6473           8579
CVE-006-2016        72682          76583              0

I hope this clarifies what I am trying to do.

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

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

Why wouldn't you want those to be rows instead of new columns? Having normalized data to work with is much easier for whatever else you need to do with this data. — Sean Lange, Jan 23 '17 at 15:39
The format of these columns [not my design] is like a date format but like this: Sept 1, 2016 in the column name is D20160901. I am trying to take that column name and convert it to 09-01-2016. The stored procedure is being used to create a report. I'm looking to create the temp table with the dates in the right format and pass two parameters for beginning and ending dates. — bbcompent1, Jan 23 '17 at 16:09
This sounds like a dynamic pivot or dynamic cross tab would be a better solution than trying to tweak a temp table like this. — Sean Lange, Jan 23 '17 at 16:11
Do you have any ideas how I could do this? I'm not too strong on pivot tables. Can the PIVOT table return results to my report query? — bbcompent1, Jan 23 '17 at 17:39
It would be the query for your report. Here is an excellent example of this. http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query/10404455#10404455 — Sean Lange, Jan 23 '17 at 17:42
Sorry, I simply could not get that working at all. It would either give me the first column or it would error out about braces, mind you there were no braces in my query. I used what they had there and changed the query to match my parameters. — bbcompent1, Jan 23 '17 at 21:16
Part of the problem is you have tagged this as sql server but you have mysql dialect in your query. (LIMIT 1 OFFSET is not standard sql and only works with mysql). But for any actual code assistance we need details as described in the link I posted previously. — Sean Lange, Jan 23 '17 at 21:30
Ok, so some of the script I'm using only applies to MySQL? Ok, that makes sense. The originating source of the information stated it was for SQL Server; apparently the poster did not know what they were talking about. I'll continue working on it and let you know if I run into anything else. Thanks. — bbcompent1, Jan 25 '17 at 14:38
??? There is no dynamic sql in what you posted. And you still have syntax crossed between sql server and mysql. Honestly the whole approach of adding columns like this is completely flawed. You should be doing a dynamic cross tab or a dynamic pivot and forget the concept of adding these columns to your temp table. If you really want some help you need to post some information. Table definitions, sample data etc. And just paraphrasing an error message is not really too helpful. Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, Jan 25 '17 at 17:22
Nobody told you to redesign your database. I suggested that adding new columns to your temp like this is fighting a losing battle. You should instead use a dynamic cross tab or dynamic pivot INSTEAD of that temp table. Or use that to CREATE the temp table. Doing it in a loop is the wrong way to go. Good luck figuring this out, I hope you can find a solution that works for you. — Sean Lange, Jan 25 '17 at 17:32
Oh ok, I see what you are saying now. I need to add a column that makes the identity unique. Gotcha, I now have my temp table set up where I have the RecID as Identity Primary Key. The thing that was frustrating me is the example had two columns but I had only one. Now that I have added this second column, I'll see how it goes. Sorry I got frustrated. — bbcompent1, Jan 25 '17 at 17:54
Hope you get is solved. If you could take the 10 minutes to put together the information required for others to help this would be pretty straight forward. But without those details it is just not possible. — Sean Lange, Jan 25 '17 at 17:57
Ok, using STUFF gave me the rows as an output to columns; however I cannot add these delimited columns using EXEC(@sql). If I do select, the variable outputs a query I can run in a window. If I try executing it, it never creates the table. — bbcompent1, Jan 31 '17 at 22:16
Actually it does create the temp table. But the scope of the temp table is limited to your dynamic sql so once you finish the execution the temp table is dropped. — Sean Lange, Jan 31 '17 at 22:17
So, what do you recommend that I do in this case? Create a real table and then drop it at the end of the proc? — bbcompent1, Jan 31 '17 at 22:29
Again....I recommend changing your approach entirely. Use a dynamic pivot instead of this crazy manipulation of your temp table. — Sean Lange, Feb 01 '17 at 13:58
I did do the dynamic pivot; however I am trying to reference the CVEId from the CVEDetails table pull the CVE Number (i.e. CVE-218-2016). Using the dynamic pivot, how would I do that? Do I need a join based on CVEId from both tables? Do you have an example that shows a join in a pivot? — bbcompent1, Feb 02 '17 at 14:06

score 0 · Accepted Answer · answered Feb 01 '17 at 12:01

This is what I ended up doing...

CREATE TABLE #Tmp
(TABLE_QUALIFIER varchar(40),
  TABLE_OWNER varchar(20),
  TABLE_NAME varchar(40),
  COLUMN_NAME varchar(40),
  DATA_TYPE int,
  TYPE_NAME varchar(20),
  PREC int, LENGTH int,
  SCALE int, RADIX int,
  NULLABLE char(4),
  REMARKS varchar(128),
  COLUMN_DEF varchar(40),
  SQL_DATA_TYPE int,
  SQL_DATETIME_SUB int,
  CHAR_OCTET_LENGTH int,
  ORDINAL_POSITION int,
  IS_NULLABLE char(4),
  SS_DATA_TYPE int)
CREATE TABLE #FixedDays  
 (COLUMN_NAME varchar(40))
CREATE TABLE #query_results
(CVENumber varchar(100) null)
Set nocount on
Insert #tmp
EXEC sp_columns @table_name = N'CVECountsByDate'
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'
DECLARE @listStr VARCHAR(MAX)
DECLARE @FixedList VARCHAR(MAX)
SELECT @FixedList = COALESCE(@FixedList,'[') + COLUMN_NAME + '] VARCHAR(MAX) NULL, [' FROM #FixedDays
SELECT @FixedList = substring(@FixedList, 1, len(@FixedList) -1)
SET @FixedList = LEFT(@FixedList, len(@FixedList)-1) -- Altered Column List
EXEC(N'ALTER TABLE #query_results ADD ' + @FixedList + '')
INSERT INTO #query_results 
SELECT CVEDetails.CVENumber, CVECountsByDate.* FROM CVECountsByDate INNER JOIN CVEDetails ON  CVECountsByDate.CVEId = CVEDetails.CVEID
ALTER TABLE #query_results DROP column CVEId

DROP TABLE #query_results
DROP TABLE #Tmp
DROP TABLE #FixedDays

Dynamically add columns to temp table based on rows in another temp table

1 Answers1