Dynamic Pivot Results to a Temp Table

Question

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

I edited this to show what worked for me. Thanks to the suggestion below that I marked as the answer. I did have to change the 'into' location to what is reflected above, and it is doing what I need it to do. Much appreciated @Teis Lindemark! — BumpML, Mar 19 '14 at 11:37
I answered a similar question [here](https://stackoverflow.com/a/35306937/1484297), I know the question generically referred to "temp table" so the above usage of global temp tables meets the criteria, but all things being equal, local temp tables are preferable to globals so lurkers should also see that question and answer. — KnarfaLingus, Feb 10 '16 at 04:50

score 6 · Accepted Answer · answered Mar 19 '14 at 11:18

6

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

answered Mar 19 '14 at 11:18

Teis Lindemark

352
5
16

1

@Ties Lindemark, thanks. I adjusted the query per your suggestion, but I get "Incorrect syntax near the keyword 'into'. Any ideas? – BumpML Mar 19 '14 at 11:25
Actually, what you posted gave me some ideas to try, and one worked. I edited my question to show what did the trick. Thank you for getting me on the right track. – BumpML Mar 19 '14 at 11:34
2

Oh, INTO should probabably be in the first select. "SELECT EVAL_ID, AuditType, ' + @cols + ' INTO from" I checked it out on a query without a pivot part. – Teis Lindemark Mar 19 '14 at 11:58
I did figure that out. As I said, your suggestion did get me to what was needed. Thanks for your help! – BumpML Mar 19 '14 at 12:36

score 0 · Answer 2 · edited Apr 13 '19 at 09:19

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

Dynamic Pivot Results to a Temp Table

2 Answers2

Linked