Thanks Martina -
That's exactly what I was trying to do. The code will run without the last select, but with it there is an error that says "Invalid object name #TEMP".

>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