Pivot/Group data by key-CodePudding

I have a table (survey_source) like so where any of the values in answer_text can be populated or NULL:

 ----------- --------------- ------------- --------------- ------------ 
| survey_id | submission_id | question_id | question_text | answer_text|
 ----------- --------------- ------------- --------------- ------------ 
| 001       | 2             | question1   | why?          | answer A   |
 ----------- --------------- ------------- --------------- ------------ 
| 001       | 2             | question2   | how?          | answer B   |
 ----------- --------------- ------------- --------------- ------------ 
| 001       | 5             | question1   | why?          | answer C   |
 ----------- --------------- ------------- --------------- ------------ 
| 001       | 5             | question2   | how?          | answer D   |
 ----------- --------------- ------------- --------------- ------------ 
| 001       | 5             | question3   | when?         | answer E   |
 ----------- --------------- ------------- --------------- ------------

I would like to pivot this data such that the new columns are survey_id, submission_id, question1, question2 and question3 such that each submission is it's own row vs earch row being it's own question per submission.

If I use CASE WHEN submission_id = 2 THEN answer_text I can pivot the columns but I still get many rows due to the NULLS for other values.

Is there a way to get desired result? The only way I can think of is to select the unique submission_id's and then left join back to the table once per question which seems like overkill.

SELECT
    t1.survey_id,
    t1.submission_id,
    t2.answer_text as question1
FROM survey_source t1
LEFT JOIN (
               SELECT
                   submission_id,
                   answer_text
               FROM survey_source
               WHERE question_id = 'question1'
            ) t2
 ON t1.submission_id = t2.submission_id

 --- Repeat join to get question2
 --- Repeat join to get question3

Desired output table like so:

 ----------- --------------- ----------- ----------- ----------- 
| survey_id | submission_id | question1 | question2 | question3 |
 ----------- --------------- ----------- ----------- ----------- 
| 001       | 2             | answer A  | answer B  | NULL      |
 ----------- --------------- ----------- ----------- ----------- 
| 001       | 5             | answer C  | answer D  | answer E  |
 ----------- --------------- ----------- ----------- -----------

CodePudding user response：

GROUP BY using MAX is a way to achieve this.

select
    survey_id,
    submission_id,
    max(case when question_id = 'question1' then answer_text end) as 'question1',
    max(case when question_id = 'question2' then answer_text end) as 'question2',
    max(case when question_id = 'question3' then answer_text end) as 'question3'
from survey_source
group by survey_id, submission_id
order by survey_id, submission_id;