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.