Getting a Dynamically-Generated Pivot-Table into a Temp Table

Question

I've seen this, so I know how to create a pivot table with a dynamically generated set of fields. My problem now is that I'd like to get the results into a temporary table.

I know that in order to get the result set into a temp table from an EXEC statement you need to predefine the temp table. In the case of a dynamically generated pivot table, there is no way to know the fields beforehand.

The only way I can think of to get this type of functionality is to create a permanent table using dynamic SQL. Is there a better way?

Answer 1

you could do this:

-- add 'loopback' linkedserver 
if exists (select * from master..sysservers where srvname = 'loopback')
    exec sp_dropserver 'loopback'
go
exec sp_addlinkedserver @server = N'loopback',
    @srvproduct = N'',
    @provider = N'SQLOLEDB', 
    @datasrc = @@servername
go

declare @myDynamicSQL varchar(max)
select @myDynamicSQL = 'exec sp_who'
exec('
    select * into #t from openquery(loopback, ''' + @myDynamicSQL + ''');
    select * from #t
    ')

EDIT: addded dynamic sql to accept params to openquery

Answer 2

Let me try this explanation of select into instead. I'm running SQL Server 2005 as well. Because you have PIVOT tables I'm going to assume the same or 2008.

select 
    o.*,
    OtherField1,
    OtherField2
INTO #temp
FROM
    OriginalOtherData as ood
PIVOT (
    MAX([Value])
    FOR Field in (OtherField1, OtherField2)
) as piv
RIGHT OUTER join
    Original o on o.OriginalSD = piv.OriginalSD

select * from #temp
Drop table #temp

The only difference between a normal select and a select into is that INTO #table part.