DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.Year)
FROM Hajj_Expenses c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT Descriptions, ' + @cols + ' from
(
select Descriptions
, ZIA
, Year
from Hajj_Expenses
) x
pivot
(
max(ZIA)
for Year in (' + @cols + ')
) p '
execute(@query)
set @query = 'SELECT Descriptions, ' + @cols + ' into ##MyTempTable from
(
select Descriptions
, ZIA
, Year
from Hajj_Expenses
) x
pivot
(
max(ZIA)
for Year in (' + @cols + ')
) p '
Member
293 Points
676 Posts
store my pivot query in temptable
Jul 09, 2020 09:07 AM|Gopi.MCA|LINK
Hello
This is my query
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX); SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.Year) FROM Hajj_Expenses c FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT Descriptions, ' + @cols + ' from ( select Descriptions , ZIA , Year from Hajj_Expenses ) x pivot ( max(ZIA) for Year in (' + @cols + ') ) p ' execute(@query)I want to store in this resulset in temptable
how would be my query
Participant
1120 Points
296 Posts
Re: store my pivot query in temptable
Jul 09, 2020 05:54 PM|imapsp|LINK
Hi,
Try using a global temporary table:
set @query = 'SELECT Descriptions, ' + @cols + ' into ##MyTempTable from ( select Descriptions , ZIA , Year from Hajj_Expenses ) x pivot ( max(ZIA) for Year in (' + @cols + ') ) p 'Hope this help