Dynamic Pivot Results to a Temp Table

问题:

While I was able to find how to pivot this data in these forums, I have not been able to find a means to push the results to a temp table so that I can use it for other queries. The code is the following. Is there a possible way to have the output of this populate a temp table?

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(QT.QUESTION_DESC)
                 FROM #QUES_TEMP QT
                 GROUP BY QT.QUESTION_DESC
                 FOR XML PATH(''), TYPE
                 ).value('.', 'NVARCHAR(MAX)')
                 ,1,1,'')

set @query = 'SELECT EVAL_ID, AuditType, ' + @cols + ' 
            into ##tmp
            from
        (
            select     QT.EVAL_ID,
                       QT.AuditType,
                       QT.SCORE,
                       QT.QUESTION_DESC

            from #QUES_TEMP QT
         ) x

        pivot
        (
             max(SCORE)
             for QUESTION_DESC in (' + @cols + ')
        ) p '

execute(@query);

select * from ##tmp

回答1:

You should be able to use INTO Clause. I added INTO into your example.

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(QT.QUESTION_DESC)
                 FROM #QUES_TEMP QT
                 GROUP BY QT.QUESTION_DESC
                 FOR XML PATH(''), TYPE
                 ).value('.', 'NVARCHAR(MAX)')
                 ,1,1,'')

set @query = 'SELECT EVAL_ID, AuditType, ' + @cols + ' from
        (
            select     QT.EVAL_ID,
                       QT.AuditType,
                       QT.SCORE,
                       QT.QUESTION_DESC
            into ##tmp
            from #QUES_TEMP QT
         ) x

        pivot
        (
             max(SCORE)
             for QUESTION_DESC in (' + @cols + ')
        ) p '

execute(@query);

SELECT * FROM ##tmp

回答2:

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(QT.QUESTION_DESC)
                 FROM #QUES_TEMP QT
                 GROUP BY QT.QUESTION_DESC
                 FOR XML PATH(''), TYPE
                 ).value('.', 'NVARCHAR(MAX)')
                 ,1,1,'')

set @query = 'SELECT EVAL_ID, AuditType, ' + @cols + ' 
            from
        (
            select     QT.EVAL_ID,
                       QT.AuditType,
                       QT.SCORE,
                       QT.QUESTION_DESC

            from #QUES_TEMP QT
         ) x

        pivot
        (
             max(SCORE)
             for QUESTION_DESC in (' + @cols + ')
        ) p '

set @query = 'select * into ##Temp from ('+@query+') y'
execute(@query)
select * from ##Temp