Combining PIVOT and INSERT queries

Can someone please help me modify the following pivot query into an INSERT INTO query (i.e. results are exported into a new table)...

SELECT RespondantID, [1]As Q1, [2]As Q2, [3]As Q3, [4]As Q4, [5]As Q5, [6]As Q6, [7]As Q7, [8]As Q8, [9]As Q9, [10]As Q10FROM (SELECT RespondantID, QuestionID, AnswerFROM [3_Temp]WHERE SurveyID=1)AS preData PIVOT (MAX(Answer)FOR QuestionIDIN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]) )AS dataORDER BY RespondantID

Thanks,

Martin

You can use a CTE and a SELECT into to get your pivot result to a new table. You need to remove ORDER BY RespondantID clause first.

Here is the sql script.

WITH mycte

AS

(SELECT RespondantID, [1]AS Q1, [2]AS Q2, [3]AS Q3, [4]AS Q4, [5]AS Q5, [6]AS Q6, [7]AS Q7, [8]AS Q8, [9]AS Q9, [10]AS Q10

FROM(SELECT RespondantID, QuestionID, Answer

FROM [3_Temp]

WHERE SurveyID= 1)AS preDataPIVOT(MAX(Answer)FOR QuestionIDIN([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]))AS data

)

SELECT RespondantID, [Q1], [Q2], [Q3], [Q4], [Q5], [Q6], [Q7], [Q8], [Q9], [Q10]INTO [NewtableResult]FROM mycte