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?

Taryn · Accepted Answer · 2012-10-04T22:01:25.917

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

Well, this is crazy.. How did you manage to write all that stuff so fast? :) — jakubka, Oct 04 '12 at 22:07
The first SQL Fiddle example does exactly what I need, thank you! However, I do not get one thing.. How does grouping by Guid works in your query? I cannot see anything related to that. — jakubka, Oct 04 '12 at 22:08
@jakubka in the last query it works because it then produces all rows. If you remove the `GROUP BY Guid` you will only get one result. The same thing will happen it you exclude `GUID` from the `SELECT` statement only one row will appear. You need to make the values distinct since there is an aggregate function and the `GUID` does that. :) — Taryn, Oct 04 '12 at 22:11

score 2 · Answer 2 · edited May 23 '17 at 11:48

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

Thanks! I did not realize I can use a harmless function like max to get a single value. :) Still, number of questions is completely unknown, so I am stuck with dynamic SQL I am afraid.. Thank you anyway! — jakubka, Oct 04 '12 at 22:09

Query to produce survey summary (PIVOT)

2 Answers2