Dynamic creation of table in tsql

Question

I am trying to create a temptable dynamically in tsql . I have a query which gives me some data (as a result of another dynamic query ) in a column. For example say years like 2011/2012 . This will be in a column. What i want is to pick value of each row and name it as a column name of type int. Later , i can use that temp table to dump data into it.

Any suggestions?

Answer 1

Would you be able to use something like this?

DECLARE @Tab VARCHAR(MAX)
SET @Tab = 'CREATE TABLE #Whatever ('

SELECT @Tab = @Tab + QUOTENAME(ColumnName) + ' INT NULL
,'
FROM @This
SELECT @Tab = SUBSTRING(@Tab,1,LEN(@Tab)-1) + ')'
PRINT @Tab
-- EXEC (@Tab)

Two points: You might find that you will need to create the temp table without using dynamic sql, then adjust the select statement to alter the table; this is so that the table exists in the current context - if the create statement is in the dynamic, you may get an error.

I don't like using dynamic sql like this - if you could use a pivot later to return the columns, that might be a better option (I'm not sure whether it's possible in this case).