sql - How do I dynamically pivot row values into columns without aggregare? -

lets have following table representing results user survey system.

surveyid    responseid  questionid  answer  --------   ----------  ----------  ------ 1           1           1           'answer 1' 1           1           2           'answer 2' 1           1           3           'answer 3' 1           2           1           'red' 1           2           2           'blue' 1           2           3           'green' 

what want pivoted output such shown below.

surveyid    responseid  q1          q2          q3 --------    ----------  --          --          -- 1           1           'answer 1'  'answer 2'  'answer 3' 1           2           'red'       'blue'      'green' 

i know how achieve if there same 3 questions database hosts multiple surveys have number of unique questionids need q1, q2, q3 columns dynamic depending upon number , ids of survey's questions.

i thought standard problem cannot find satisfies issue. solution must work sql server 2005.

hope makes sense. thanks.

1) pivots need aggregate. may know in advance interested in 1 row, sql doesn't know that. if dealing 1 row per group, use min() aggregate.

2) dynamic pivot not standard problem sql. that's task presentation layer, not data layer. have use dynamic sql, still won't able handle arbitrary number of columns , open injection attacks if aren't careful.

if still want way:

create table #t (surveyid int, responseid int, questionid int, answer varchar(max)) insert #t values (1,1,1,'answer1'),(1,1,2,'answer2'),(1,1,3,'answer3'),(1,2,1,'red'),(1,2,2,'blue'),(1,2,3,'green')  declare @qids nvarchar(4000)  select @qids = coalesce(@qids+',','') + qid (select distinct quotename(questionid) qid #t) t  exec ('select [surveyid],[responseid],'+@qids+' #t pivot(min(answer) questionid in('+@qids+')) p')