How to insert into a table instead of select in SQL Server-CodePudding

I have a XML transposing rows into column query. When I execute it, I can view the data. However, instead of select, I want the query to create a table using @cols statement and then insert the data into that table by executing @query.

This is my current code:

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);

SET @cols = STUFF((SELECT 
                       ',' QUOTENAME(c.[fieldname])
                   FROM #specnorm c
                   GROUP BY c.fieldname
                   ORDER BY MIN(sourceSequenceTypical) 
                   FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '');

SET @query = 'SELECT '   @cols   'FROM (SELECT 
       [rownumber],
       [contents],
       [fieldname] 
   FROM #rawtemp
   ) x PIVOT (MAX(contents) FOR fieldname IN ('   @cols   ')) p'

EXECUTE (@query);

CodePudding user response：

try:

SET @query = 'SELECT ' @cols ' into YOURTABLE from (SELECT 
   [rownumber],
   [contents],
   [fieldname] 
FROM #rawtemp
) x pivot (max(contents) for fieldname in (' @cols ')) p'

it creates YOURTABLE on the fly