Build dynamic temp table to host a dynamic pivot table

Transact-SQL
1

I build a Pivot Table that returns a dynamic amount of columns. The amount of columns and their names are determined by the content of a table "Features" I populate previously. The records for the column "feature" are the column names of the Pivot Table.

I need to create a temp table I can insert the pivot result into. But how can I build a table with dynamic columns coming from the query Select feature from Features?

Martin

2

I'm trying to achieve this with a @sql string but it throws an error near keyword declare

DECLARE @sql NVARCHAR(MAX) = 'CREATE TABLE vainas ( [' + (


DECLARE @MyCursor CURSOR;
DECLARE @MyField NVARCHAR(MAX);
BEGIN
SET @MyCursor = CURSOR FOR
SELECT feature from Features      

OPEN @MyCursor 
FETCH NEXT FROM @MyCursor 
INTO @MyField

WHILE @@FETCH_STATUS = 0
BEGIN

  SELECT @MyField 

  FETCH NEXT FROM @MyCursor 
  INTO @MyField 
END; 

CLOSE @MyCursor ;
DEALLOCATE @MyCursor;
END;


   ) + '] NVARCHAR(100))'

EXECUTE sp_executesql @sql;
3

This is a duplicate post. I recommend we keep it all together at the other post, which is located at http://forums.sqlteam.com/t/loop-through-results-returned-by-pivot-table/6581

4

I don't agree... this is just on the creation of the dynamic Table. But I got confused and posted this on the other one.

I finally managed to create the table:

Declare @Columns nvarchar(MAX)
set @Columns = (SELECT stuff((select ', ' + '['+Tag+'] NVARCHAR(100)' from Model_tags for xml path('')),1,1,''))

DECLARE @sql NVARCHAR(MAX) =
 'CREATE TABLE Vainas ('+ @Columns + ')'
EXECUTE sp_executesql @sql;
5

Try using QUOTENAME(Tag) instead