A place I used to work at had user-defined tables within the database, so users could expand the reporting beyond what was supplied as a default. So I've encountered the same issue myself.

I don't have the code anymore but it basically involved extracting the column names into a table, building a variable with those comma-seperated names and then running a dynamic sql statement:

DECLARE @SQL AS VARCHAR(500), @Stuff AS VARCHAR(500)

CREATE TABLE #TestTable(

Column1INT,

Column2INT);

SELECT @Stuff = STUFF((SELECT ', ' + column_name

FROM tempdb.INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME like '#testtable%'

ORDER BY column_name

FOR XML PATH('')), 1, 2, '');

SET @SQL = 'select *

into #temp

from #source

pivot ( avg(value) for drive in (' + @Stuff + ' )) as value'

SELECT @SQL;

DROP TABLE #TestTable

Because you have a temp table it may be a little more involved when identifying the correct temp table, because there may be several of these tables in tempdb at the same time, if several copies of this proc are executing. I think I dynamically generated a table name, so it was always unique within each run of the proc.