Dynamic Stored Procedure Results to Table

Question

I have a stored procedure that dynamically produces pivot results, passing sql for row defs, column to pivot, aggregate(field) to sum, and table name of aggregate. This works great, but i need to produce a table from these results to use in further calculations.

How can I dynamically save the results to a table within the stored procedure (temp or non temp) without knowing the output columns??

Answer 1

SELECT *
INTO #TempTable
FROM (Pivot Expression)

This will create a #TempTable with the results of whatever you have in the FROM clause, regardless of number/type of columns.

Answer 2

you didn't ask, but this is how I'm getting a set of column names from a view:

DECLARE @columns VARCHAR(1000)

SELECT @columns = COALESCE(@columns + ',[' + cast(fld as varchar) + ']',
'[' + cast(fld as varchar)+ ']')
FROM view
GROUP BY fld