T-SQL Pivot? Possibility of creating table columns from row values

Is it actually possible to rotate a T-SQL (2005) so that (for the sake of argument) the values of the first column's rows become the titles of the output table's columns?

I realise this is not really what PIVOT is for, but it's what I need - the ability to request a table where the columns are not known before-hand because they have been entered as values into a table.

Even a hack would be nice, tbh.