Create table on SQL Server from dynamic pivot results

Question

Is there a way to directly store the results of a dynamic pivot query into a fixed table? As the result is dynamic I can't create the table by specifying the columnnames and methods like "create table MyTable as (pivot select statement)" seem to fail on SQL server ("Incorrect syntax near the keyword 'AS'"). I have tried to format the SQL below to get a SELECT - INTO - FROM structure but failed to do so. Any help is obviously greatly appreciated!

The SQL used for the pivot is (build thanks to this great website!):

declare @pivot varchar(max), @sql varchar(max)
create table pivot_columns (pivot_column varchar(100))

insert into pivot_columns
select distinct DateField from MyTable order by 1

select @pivot=coalesce(@pivot+',','')+'['+pivot_column+']'from pivot_columns

set @sql = 'SELECT * FROM (select DateField, RefCode, SumField from MyTable) p
PIVOT
(sum(SumField) FOR DateField IN  ( ' + @pivot + ') ) 
AS pvl'

drop table pivot_columns

exec (@sql)

Answer 1

Unless I am not following what you are trying to do you should be able to add the INTO mynewTable to your sql that you are going to execute and you should get the new table.

declare @pivot varchar(max), @sql varchar(max)
create table pivot_columns (pivot_column varchar(100))

insert into pivot_columns
select distinct DateField from MyTable order by 1

select @pivot=coalesce(@pivot+',','')+'['+pivot_column+']'from pivot_columns

set @sql = 'SELECT * INTO mynewTable FROM (select DateField, RefCode, SumField from MyTable) p
PIVOT
(sum(SumField) FOR DateField IN  ( ' + @pivot + ') ) 
AS pvl'

drop table pivot_columns

exec (@sql)

I just test creating a new table in the following script and it gives me a new table that is in the DB for use:

create table t
( 
    [month] int, 
    [id] nvarchar(20), 
    [cnt] int 
)

insert t values (4,'TOTAL',214)
insert t values (5,'TOTAL',23)
insert t values (6,'TOTAL',23)
insert t values (4,'FUNC',47)
insert t values (5,'FUNC',5)
insert t values (6,'FUNC',5)
insert t values (4,'INDIL',167)
insert t values (5,'INDIL',18)
insert t values (6,'INDIL',18)


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(month) 
            FROM t 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT *
            INTO tabletest from 
            (
                select month, id, cnt
                from t
           ) x
            pivot 
            (
                 sum(cnt)
                for month in (' + @cols + ')
            ) p '


execute(@query)

drop table t