sql - Insert query results into temp table -

i've learned here how pivot in sql, , took example other question on here. works perfectly.

however, want perform additional joins, after query, unable insert temporary table results of query? how may that?

create table

create table yt ([store] int, [week] int, [xcount] int) ; insert yt ([store], [week], [xcount]) values (102, 1, 96), (101, 1, 138), (105, 1, 37), (109, 1, 59), (101, 2, 282), (102, 2, 212), (105, 2, 78), (109, 2, 97), (105, 3, 60), (102, 3, 123), (101, 3, 220), (109, 3, 87); 

perform pivoting query

declare @cols nvarchar(max), @query  nvarchar(max)  select @cols = stuff((select ',' + quotename(week)                  yt                 group week                 order week         xml path(''), type         ).value('.', 'nvarchar(max)')      ,1,1,'')   set @query = 'select store,' + @cols + '           (             select store, week, xcount             yt         ) x         pivot          (             sum(xcount)             week in (' + @cols + ')         ) p '   execute(@query) 

the result is

store   1   2   3 101 138 282 220 102 96  212 123 105 37  78  60 109 59  97  87 

but id have in #temp table, , tried placing #temp before 'execute query' , before statement within query.

any idea? aware of select * #temp blabla seems diff queries.

you can create global temp table dynamically

  declare @tblname nvarchar(10)   set @tblname = n'##temp' + cast(@@spid nvarchar(5))   declare @tblcreate nvarchar(max)   set @tblcreate = n'create table ' + @tblname + ' ('    + replace(@cols,',',' int,') + n' int)'    execute sp_executesql @tblcreate 

and edit @query insert table.

set @query = 'insert ' + @tblname + ' select store,' + @cols + '           (             select store, week, xcount             yt         ) x         pivot          (             sum(xcount)             week in (' + @cols + ')         ) p;          drop table ' + @tblname   execute(@query)