Tricky SQL Server Pivot Table for Survey

Question

I have a SQL Server view with following data:

ID   clientID  surveyID    questionID     q_optionID   q_ans_text
-----------------------------------------------------------------
1       1          1           1            NULL            Yes
2       1          1           2             18             NULL 
3       1          1           3             19             NULL
4       2          1           1            NULL             No
5       2          1           2             18             NULL
6       2          1           3             19             NULL
7       3          2           1            NULL            Yes 
8       3          2           2             15             NULL 
9       3          2           3             13             NULL   

I want the result to be something like this:

ClientID  SurveyID   Q1    Q2    Q3  
------------------------------------
   1          1     Yes    18    19   
   2          1      No    18    19   
   3          2     Yes    15    13 

Where the conditional NULL values are ignored and the proper answer is placed in the column. I have looked at Pivot table examples, but they seem to be focusing on single column pivots.

Answer 1

select
  clientid, surveyid,
  max(case when questionid = 1 then coalesce(q_ans_text, cast(q_optionID as char)) else null end) as Q1,
  max(case when questionid = 2 then coalesce(q_ans_text, cast(q_optionID as char)) else null end) as Q2,
  max(case when questionid = 3 then coalesce(q_ans_text, cast(q_optionID as char)) else null end) as Q3
 from yourtable
 group by clientid, surveyid

http://sqlfiddle.com/#!18/9163ba/11

Answer 2

In order to perform this transformation you will need to UNPIVOT and then PIVOT the data. The UNPIVOT will take the values from your q_optionID and q_ans_text columns and transform it into two columns one with the value and the column name.

There are two ways that you can PIVOT this, you can hard-code all of the values using a static version or you can use dynamic sql. In order to UNPIVOT the data you need to be sure that the data is of the same datatype, so converting might be necessary.

Static PIVOT:

select clientid, surveyid, 
    questionid,
    value,
    col
  from
  (
    select clientid, surveyid, questionid,
      cast(q_optionID as varchar(4)) q_optionID,
      q_ans_text
    from yourtable
  ) s
  unpivot
  (
    value
    for col in (q_optionID, q_ans_text)
  ) un

See SQL Fiddle with Demo

Unpivot result:

| CLIENTID | SURVEYID | QUESTIONID | VALUE |        COL |
---------------------------------------------------------
|        1 |        1 |          1 |   Yes | q_ans_text |
|        1 |        1 |          2 |    18 | q_optionID |
|        1 |        1 |          3 |    19 | q_optionID |
|        2 |        1 |          1 |    No | q_ans_text |
|        2 |        1 |          2 |    18 | q_optionID |
|        2 |        1 |          3 |    19 | q_optionID |
|        3 |        2 |          1 |   Yes | q_ans_text |
|        3 |        2 |          2 |    15 | q_optionID |
|        3 |        2 |          3 |    13 | q_optionID |

Then you will apply the PIVOT to the result to get your final product.

select *
from
(
  select clientid, surveyid, 
    'Q'+cast(questionid as varchar(10)) question,
    value
  from
  (
    select clientid, surveyid, questionid,
      cast(q_optionID as varchar(4)) q_optionID,
      q_ans_text
    from yourtable
  ) s
  unpivot
  (
    value
    for col in (q_optionID, q_ans_text)
  ) un
) src
pivot
(
  max(value)
  for question in (Q1, Q2, Q3)
) piv

See SQL Fiddle with demo

Dynamic PIVOT:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME('Q'+cast(questionid as varchar(10))) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT clientid, surveyid,' + @cols + ' from 
             (
                select clientid, surveyid, 
                  ''Q''+cast(questionid as varchar(10)) question,
                  value
                from
                (
                  select clientid, surveyid, questionid,
                    cast(q_optionID as varchar(4)) q_optionID,
                    q_ans_text
                  from yourtable
                ) s
                unpivot
                (
                  value
                  for col in (q_optionID, q_ans_text)
                ) un
            ) x
            pivot 
            (
                max(value)
                for question in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo

UNION ALL/Aggregate with Case Version:

Now, if you are working in a system that does not have PIVOT then you can use a UNION ALL to UNPIVOT and an aggregate function with a CASE to PIVOT:

select clientid, surveyid,
  max(case when questionid = 1 then value end) Q1,
  max(case when questionid = 2 then value end) Q2,
  max(case when questionid = 3 then value end) Q3
from
(
  select clientid, surveyid, questionid, cast(q_optionID as varchar(10)) value, 'q_optionID' col
  from yourtable
  union all
  select clientid, surveyid, questionid, q_ans_text value, 'q_ans_text' col
  from yourtable
) unpiv
group by clientid, surveyid

See SQL Fiddle with Demo

All three will produce the same result:

| CLIENTID | SURVEYID |  Q1 | Q2 | Q3 |
---------------------------------------
|        1 |        1 | Yes | 18 | 19 |
|        2 |        1 |  No | 18 | 19 |
|        3 |        2 | Yes | 15 | 13 |

Answer 3

Example without an explicit unpivot. Still a very useful technique to know, though!

Select
  clientID, 
  SurveyID, 
  [1] as Q1, 
  [2] as Q2,
  [3] as Q3
From (
  Select
    clientID,
    surveyID,
    questionID,
    IsNull(Cast(q_optionID as varchar(10)), q_ans_text) answer
  From
    Answers
) a
Pivot (
  Max(answer)
  For questionID In ([1], [2], [3])
) p

http://sqlfiddle.com/#!6/8552a/8