Search code examples
sql-server-2008pivot-tabletemp-tables

Storing records to temporary table inside dynamic query when using Pivot.

I am new to sql programming.

I want to store the records returned as result set from the pivot query.

Pivot's columns are selected dynamically.

My query is: 

declare @cols nvarchar(max)

set @cols=STUFF((select distinct ',[' + LTRIM(rtrim(year(Dt)))+']' from temp FOR XML PATH('')),1,1,'');

EXEC('select * from 
(select YEAR(Dt)[year],Software,Ver from temp)T
Pivot(count([year]) for [year] in ('+@cols+'))PVT')

I want to store this result to the temporary table for further reporting.

Please help me. Thanks in advance.

Solution

  • Try the below query. You will have to use INTO.

    declare @cols nvarchar(max)

set @cols=STUFF((select distinct ',[' + LTRIM(rtrim(year(Dt)))+']' 
from temp 
FOR XML PATH('')),1,1,'');

EXEC('select * INTO ##temptable from 
    (select YEAR(Dt)[year],Software,Ver from temp)T
    Pivot(count([year]) for [year] in ('+@cols+'))PVT')