Query to produce survey summary (PIVOT)

Question

Let's suppose I have two tables:

Question (Id, Text)
Answer (Value, QuestionId, Guid)

Column Guid groups answers from the same person.

I need a query to produce results like this:

'Question 1' | 'Question 2'
4            | 3
1            | NULL
NULL         | 5
2            | 6
9            | NULL

Questions texts are transformed into column headers and answers values are in rows. Answers are grouped by Guid, so there are answers from one person in one row. If a person didn't answer particular question, NULL is returned.

Number of questions can vary.

Data used to produce sample results:

 Question
 Id   | Text
 1    | Question 1
 2    | Question 2

 Answer
 Value | QuestionId | Guid
 4     | 1          | AAA
 3     | 2          | AAA
 1     | 1          | BBB
 5     | 2          | CCC
 2     | 1          | DDD
 6     | 2          | DDD
 9     | 1          | EEE

Can you please help me out with a query to produce the results?

Answer 1

If you don't wish to hard code the Question Numbers, then you're limited to using dynamic SQL to build up the question list.

SQL Server dynamic PIVOT query?

For specific questions, if you know their texts, see sample below

create table Question(id int, text varchar(100));
insert Question select 1, 'Question 1'
union all select 2, 'The 2nd';
create table Answer(
  value int,
  questionid int,
  guid varchar(10));
insert Answer select
 4     , 1          , 'AAA' union all select
 3     , 2          , 'AAA' union all select
 1     , 1          , 'BBB' union all select
 5     , 2          , 'CCC' union all select
 2     , 1          , 'DDD' union all select
 6     , 2          , 'DDD' union all select
 9     , 1          , 'EEE';
GO
select guid, [Question 1], [The 2nd]
from (
    select guid, text, value
    from Answer A
    join Question Q on A.questionid=q.id) p
pivot (max(value) for text in ([Question 1], [The 2nd])) v

Answer 2

Since you have an unknown number of questions then tou will need to use dynamic SQL to PIVOT this:

DECLARE @colsFinal AS NVARCHAR(MAX),
    @colsPivot AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @colsFinal = STUFF((SELECT distinct ',' 
                      + QUOTENAME(Id) 
                           + ' as Question_'+ cast(Id as varchar(10))
                    from question
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsPivot = STUFF((SELECT distinct ',' 
                      + QUOTENAME(Id) 
                    from question
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ' + @colsFinal + ' from 
             (
                select questionid, value, guid
                from question q
                left join value v
                  on q.id = v.questionid
            ) x
            pivot 
            (
                min(value)
                for questionid in (' + @colsPivot + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo

If you had a known number of columns, then you could hard-code the values for the PIVOT (See SQL Fiddle With Demo):

select [1] as Question1, [2] as Question2
from 
(
  select questionid, value, guid
  from question q
  left join value v
    on q.id = v.questionid
) x
pivot
(
  max(value)
  for questionid in ([1], [2])
) p

Or you can use an aggregate function with a CASE (See SQL Fiddle With Demo):

select max(case when q.id = 1 then v.value end) Question1,
  max(case when q.id = 2 then v.value end) Question2
from question q
left join value v
  on q.id = v.questionid
group by guid