Ralph Ralph - 5 months ago 10
PHP Question

Pivot table in a specific way in MySQL

There are other pivot table questions but I can't seem to find an answer to my question.

This is my table

ID QUESTION ANSWER RECORDID SORTORDER
1 Question 1 Answer 1.1 123456 1
2 Question 2 Answer 2.1 123456 2
3 Question 3 Answer 3.1 123456 3
4 Question 1 Answer 1.2 654321 1
5 Question 2 Answer 2.2 654321 2
6 Question 3 Answer 3.2 654321 3


Etc.

I would like to output it with a query to:

Question 1 Question 2 Question 3
Answer 1.1 Answer 2.1 Answer 3.1
Answer 1.2 Answer 2.2 Answer 3.2


So in short, the question column holds questions that are not unique (in this demo there are 2 entries (see RECORDID)) so they need to be grouped and sorted by sortorder. RECORDID is always unique

I've seen some pivot/unpivot examples but can't get my head around it.
Can someone help me with this?

Answer

This should do the trick. In the inner query you get only the answers for the specific question and in the outer query when you group by the recordid you get rid of the null values... Try running only the inner query to get the idea.

select  
  max(q.Question1),
  max(q.Question2),
  max(q.Question3) FROM (SELECT     
                         recordid,
                         case when question = 'Question 1' then answer else null end as Question1, 
                         case when question = 'Question 2' then answer else null end as Question2, 
                         case when question = 'Question 3' then answer else null end as Question3 FROM questions) q group by q.recordid

Let me know if you have any quesitons!

EDIT: Your comment adds another level of complexity :) Now I see how we can use that sort order column... We need one more inner query to get the questions and their sort order. Then get the answers per each sort order and then group by the record id to filter out the nulls and get the desired result. Please try this out and let me know how it goes... The number of questions in the select statements has to be equal to the number of questions of the form which has the most questions - I've put in 8 just to show you that this should not constrain you. Now you won't depend on the question name - just the sort numbering. It's good that you have the sort order - otherwise you'd have to generate a row number per each record id...

SET @rank=0; SET @id:=0; select recordid, max(qq.question1) as 'Question 1', max(qq.question2) as 'Question 2', max(qq.question3) as 'Question 3', max(qq.question4) as 'Question 4', max(qq.question5) as 'Question 5', max(qq.question6) as 'Question 6', max(qq.question7) as 'Question 7', max(qq.question8) as 'Question 8' FROM ( SELECT recordid, case when q.rownumber = 1 then CONCAT(question,': ', answer) else null end as question1, case when q.rownumber = 2 then CONCAT(question,': ', answer) else null end as question2, case when q.rownumber = 3 then CONCAT(question,': ', answer) else null end as question3, case when q.rownumber = 4 then CONCAT(question,': ', answer) else null end as question4, case when q.rownumber = 5 then CONCAT(question,': ', answer) else null end as question5, case when q.rownumber = 6 then CONCAT(question,': ', answer) else null end as question6, case when q.rownumber = 7 then CONCAT(question,': ', answer) else null end as question7, case when q.rownumber = 8 then CONCAT(question,': ', answer) else null end as question8 FROM( select recordid, question, answer, sortorder, @rank:=CASE WHEN @id=recordid THEN @rank+1 ELSE 1 END as rownumber, @id:=recordid from questions order by recordid, sortorder ) q )qq GROUP BY recordid

Comments