use pivot table in stored procedure

Question

I have a pivot query that works great!

;with PivotData As (
Select KeyField, ColumnName, FieldValue FROM
(SELECT SomeField as KeyField, NameField As ColumnName, DataField as FieldValue
FROM Table1
UNION
SELECT SomeField, NameField, DataField
FROM Table2)
SELECT [List of Fields] FROM PivotData
PIVOT (Max(PivotData.FieldValue) for ColumnName In ([List of Fields])) As P

Now I want to use that query as the source of a temp table in a stored procedure and none of the syntax that I have tried to INSERT the results of the query into a temp table have worked.

Can I create a temporary table from the results? If so, how?

Thanks!

Leslie

Answer 1

Please try this :

;with PivotData As (
Select KeyField, ColumnName, FieldValue FROM
(SELECT SomeField as KeyField, NameField As ColumnName, DataField as FieldValue
FROM Table1
UNION
SELECT SomeField, NameField, DataField
FROM Table2)
SELECT [List of Fields] into #temp FROM PivotData
PIVOT (Max(PivotData.FieldValue) for ColumnName In ([List of Fields])) As P

here you dont have to declare a table or table variable specifically for the purpose.