Create temporary table with dynamic number of columns

Question

I'm trying to create a temporary table with a dynamic number of columns:

set @cmd = ' SELECT * into #temp3 from
            (
                select * from sometable
            ) x pivot
            (
                max(buildrate)
                for name in ('+ @columns +')
            ) as y '

execute(@cmd);

select * from #temp3 left join performed in an elegant way... 

and I need to use contents from that table in other processing.

Is there any reasonable way to do this?

Answer 1

One workaround is to use global temporary table:

SET @cmd = ' SELECT * INTO ##temp3 FROM
            (
                select * from sometable
            ) x pivot
            (
                max(buildrate)
                for name in ('+ @columns +')
            ) as y '

EXECUTE(@cmd);

SELECT *
INTO #temp3
FROM ##temp3;

DROP TABLE ##temp3;

SELECT *
FROM JOIN #temp3 
LEFT ...;

The normal local temporary table won't work, because Dynamic SQL creates new context. The table is in that context and will cease to exist when code is executed, so you cannot use it outside Dynamic-SQL.