Hi Don,

set @SQL = '
select *  INTO #TEMP  from ( 
SELECT 5 AS myorder, 
''AltModeParticipation'' as Label, 
[Year],
AltModeParticipation 
FROM HistoricalSummary 
WHERE CompanyName = @CompanyName
) Table1 
pivot (sum(AltModeParticipation) for year in (' + @Columns + ')) p5 
'	
execute sp_ExecuteSQL @SQL, N'@CompanyName  nvarchar(65)', @CompanyName ;
<strong>SELECT * FROM #TEMP</strong>

MartinaJ

>Hi, I'm still trying to find a best solution to this. I have this simplified query that works OK.
>

>set @SQL = 
>'
>select * INTO #TEMP from ( 
>
>SELECT 5 AS myorder, 
>''AltModeParticipation'' as Label, 
>[Year],
>AltModeParticipation 
>FROM HistoricalSummary 
>WHERE CompanyName = @CompanyName
>) Table1 
>pivot (sum(AltModeParticipation) for year in (' + @Columns + ')) p5 '	
>	
>execute sp_ExecuteSQL @SQL, N'@CompanyName  nvarchar(65)', @CompanyName ;	

>
>However, when I try to access #TEMP with a followup select or insert I get an error that the object does not exist. Since it seems the original query executes OK, what happens to the tempory table? I assume what is happening is that the query is creating a temp connection that is closed after the execute and taking #TEMP with it. Does anyone know how I can hang onto it?
>
>Thanks