How To Save Dynamic Pivot Variable Output As SQL Server Table

Question

I have successfully constructed the output that I have been looking for from using dynamic SQL to create a pivot table with dynamically created column names.

My code is:

IF OBJECT_ID('tempdb..#TempDB') IS NOT NULL
DROP TABLE #TempDB
SELECT CASEID, FORMNAME, NAME, VALUE INTO #TempDB FROM dbo.EFORM WHERE FORMNAME='IncidentReporting'

 IF OBJECT_ID('tempdb..#TempDB1') IS NOT NULL
DROP TABLE #TempDB1
SELECT DISTINCT Name INTO #TempDB1 FROM #TempDB

DECLARE @columns varchar(max)
DECLARE @query varchar(max)

SELECT @columns = COALESCE(@columns + ',[' + cast([Name] as varchar(100)) + ']', 
             '[' + cast([Name] as varchar(100))+ ']') 
             FROM #TempDB1

SET @query = 'SELECT * FROM #TempDB AS PivotData '
SET @query = @query  + 
'PIVOT (MAX(VALUE) FOR [NAME] IN (' + @columns + ')) AS p'

EXEC (@query)

This successfully gives me results like:

CASEID  FORMNAME             Column1    Column2 Column3
501000000621    IncidentReporting   Value1  Valuea  Valuev
501000000622    IncidentReporting   Value2  Valueb  Valuew
601000000126    IncidentReporting   Value3  Valuec  Valuex
601000000127    IncidentReporting   Value4  Valued  Valuey
601000000128    IncidentReporting   Value5  Valuee  Valuez

These results, outputed from the @query variable, are in exactly the format that I want a table of these results to be in.

Can anyone tell me how to get the results that are in the @query variable into a standard SQL table?

I have tried doing a statement like this, but I get the message "Incorrect syntax near ' + @columns + '":

SELECT *
INTO #TempDB4
FROM (SELECT * FROM #TempDB AS PivotData 
PIVOT (MAX(VALUE) FOR [NAME] IN (' + @columns + ')) AS p)

Many thanks in advance.

Tim Lehner · Accepted Answer · 2012-11-16 16:02:57Z

2

In your existing code, add your into to this line:

SET @query = 'SELECT * FROM #TempDB AS PivotData '

so that you get:

SET @query = 'SELECT * INTO #TempDB4 FROM #TempDB AS PivotData '

Or add insert in the same manner.

To get your unsuccessful query to work as you expect, you'd have to turn that into dynamic SQL, much like your successful query, and call it using exec or sp_executesql

answered Nov 16, 2012 at 16:02

Tim Lehner

15.3k4 gold badges61 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Craig C Over a year ago

Thank you so much for this. You have no idea how much Googling failed to come up with this simple solution. Works perfectly!

user890332 Over a year ago

In order for this to work, you need to make it a global temp variable. ##temp

Collectives™ on Stack Overflow

How To Save Dynamic Pivot Variable Output As SQL Server Table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related