Dynamic SQL Pivot with no Aggregate

Imagine a Survey database. The layout of the database is meant to be reusable, DRY-conforming and fast. Meaning it's not very user-friendly. A data dump would be a PITA for any user to interpret.

A database diagram is below.

One thing that I could not find out how to do online was how to write a PIVOT query without using an aggregate function. The reason is that you cannot. You have to aggregate the data. So the trick is to write your query with only one column of results being pivoted -- making the aggregate meaningless.

Another issue I have with PIVOTs is that you need to know something about your DataSet in order to pivot it. That is lame. So the following TSQL query will get a list of all users, their answers to the survey & the questions as columns. It does this by building the PIVOT statement in SQL and then executing it.

/*****************************************
*
* WARNING
* If you receive the following error: 
* Msg 102, Level 15, State 1, Line 4 Incorrect syntax near '('.
* THEN your database is NOT configured with SQL Server 2005 
* Compatibility-Level and PIVOT is not recognized
* 
******************************************/

if object_id('tempdb..#pivotdata') is not null
begin
 drop table #pivotdata
end

declare @questions table( id int identity(1,1), question varchar(500) )
declare @i int
declare @cmd varchar(8000)

select
u.Reference, u.Email, FirstName, Question, Answer
into #pivotdata
from surveyresult r
join surveyuser u on u.[id] = r.surveyuserid
join surveyanswer a on a.[id] = r.answerid 
join surveyquestion q on q.[id] = a.questionid 
order by FirstName


insert into @questions
select distinct question from #pivotdata

set @i = 1
set @cmd = '
   select *
   from #pivotdata
   pivot ( MAX(answer) FOR question IN 
   ('

while @i < (select max(id) from @questions) + 1
begin
 set @cmd = @cmd + '[' + (select question from @questions where id = @i) + '],'
 set @i = @i + 1
end
set @cmd = substring(@cmd, 0, len(@cmd)) + ') ) as p order by email'

execute(@cmd)

if object_id('tempdb..#pivotdata') is not null
begin
 drop table #pivotdata
end

Keep in mind that in order for execute to read from your temp table it must be an on-disk temporary table. No in-memory tables (like @questions) will work.