How to Pivot the SQL Data?

Question

I have a dataset related to surveys and I have to create a view on them. The dataset is in the format:

surveyID    RID    Question   Answer     Comment
-----------------------------------------------------------------
17     |   123  |   Q1     |   0      |
17     |   123  |   Q2     |          |   The salesperson and manager was very considerate and I will be coming back for my next car!
17     |   123  |   Q3     |   5      |   Very bad behavior

The required result is as

surveyID |   RID  |  Q1  |  Q1_c |  Q2  |  Q2_c  |  Q3  |  Q3_c 
-----------------------------------------------------------------
17       |   123  |  0   |       |      | The... |  5   | Very...

The Questions and questions comment should be in the header row as above:

I have tried to get the result but I succeed only if I pivot for 1 column i.e the Answer column but how to pivot the answer and comment columns simultaneously?

This is the query which I have done

select rid, surveyid, --comment,    
    Q1,Q2,Q3
from
(
  select rid, surveyid, question, --comment,    
    value
  from
  (
    select rid, surveyid, question, -- comment,
      answer      
    from #temp
  ) s
  unpivot
  (
    value
    for col in (answer)
  ) un
) src
pivot
(
  max(value)
  for question in (Q1, Q2, Q3)
) piv

And the result is as:

surveyID |   RID  |  Q1  |  Q2  |  Q3  |  
-----------------------------------------------------------------
17       |   123  |  0   |      |  5   | 

Answer 1

Here's another alternative, since SQL Server doesn't have an easy way to do a multi-column pivot with the pivot operator, you can always revert to performing a hand coded pivot"

select surveyID, rid
     , max(case question when 'Q1' then answer end) q1
     , max(case question when 'Q1' then comment end) q1_c
     , max(case question when 'Q2' then answer end) q2
     , max(case question when 'Q2' then comment end) q2_c
     , max(case question when 'Q3' then answer end) q3
     , max(case question when 'Q3' then comment end) q3_c
  from #temp
 group by surveyID, rid

It's actually more compact than the other options presented so far and possibly has better performance, though you'd need to test that assertion for yourself.

Answer 2

When you unpivot your data, you can combine the question and Col columns into a single column for use in the pivot operation like this:

select * from (
  select surveyid, rid, question+'_'+col QC, value from (
    select rid, surveyid, question, Comment,
      cast(answer as varchar(91)) Answer
    from #temp
  ) s
  unpivot
  (
    value
    for col in (Answer, Comment)
  ) un
  ) src
 pivot
(
  max(value)
  for QC in (Q1_Answer, Q1_Comment, Q2_Answer, Q2_Comment, Q3_Answer, Q3_Comment)
) piv

You can get your desired column headings with some minor tweaks:

select * from (
  select surveyid, rid, question+case col when 'comment' then '_c' end QC, value from (
    select rid, surveyid, question, Comment,
      cast(answer as varchar(91)) Answer
    from #temp
  ) s
  unpivot
  (
    value
    for col in (Answer, Comment)
  ) un
  ) src
 pivot
(
  max(value)
  for QC in (Q1, Q1_C, Q2, Q2_C, Q3, Q3_C)
) piv

Answer 3

I split the query into two similar pivots, pivot the data in CTEs, and then join the CTEs together.

;WITH QnA AS
(
    SELECT
        RID
        ,surveyID
        ,Q1
        ,Q2
        ,Q3
    FROM
    ( SELECT RID, surveyID, Question, Answer FROM #temp ) src
    PIVOT
    (
        MAX(Answer)
        FOR Question IN ( Q1
            ,Q2
            ,Q3
        )
    ) piv
)
,QnAComments AS
(
    SELECT
        RID
        ,surveyID
        ,Q1_c = Q1
        ,Q2_c = Q2
        ,Q3_c = Q3
    FROM
    ( SELECT RID, surveyID, Question, Comment FROM #temp ) src
    PIVOT
    (
        MAX(Comment)
        FOR Question IN ( Q1
            ,Q2
            ,Q3
        )
    ) piv
)
SELECT
    QnA.surveyID
    ,QnA.RID
    ,Q1
    ,Q1_c
    ,Q2
    ,Q2_c
    ,Q3
    ,Q3_c
FROM QnA
INNER JOIN QnAComments ON QnAComments.RID = QnA.RID
                           AND QnAComments.surveyID = QnA.surveyID