Pivot table in a specific way in MySQL

Question

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?

You need [unpivot](http://stackoverflow.com/questions/15184381/mysql-turn-table-into-different-table)! — Maciej Los, Jun 02 '16 at 14:54
You've tagged with PHP, so handle the presentation side of things there. Patently, RECORDID is NOT always unique! — Strawberry, Jun 02 '16 at 16:01
Also, I don't understand how two records in the same question can occupy the same sort order. — Strawberry, Jun 02 '16 at 16:23
@Strawberry your correct, I explained it the wrong way. When I said that it's unique I meant that every entry (in this case the 3 questions together are 1 entry) has 1 unique RECORD ID (the entry with ID 123456 and the entry with 654321). — Ralph, Jun 02 '16 at 18:08
OK, so what determines that Answer 1.1 appears before Answer 1.2 — Strawberry, Jun 02 '16 at 19:16

Koshera · Accepted Answer · 2016-06-02T21:13:02.493

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

Hi Koshera, thank you so much for trying to find a solution. The problem is the questions are never predictable, so "Question 1" in the query won't work. This is what happens: This table stores entries (a set of questions with 1 specific recordid) of people who used an online form to submit this data, but while the questions in the QUESTIONS column are repetitive (a form has a specific set of questions) but users can also fill in other forms that goes into this table hence the non-predictable questions. — Ralph, Jun 02 '16 at 18:09
Please see the edit - I think it should be all good now! Let me know how it goes :) — Koshera, Jun 02 '16 at 19:33
Hi, that's almost it! 2 problems remain: 1. the output says question1, question2 etc but not the actual questions (for example: first name (question 1), last name (question 2) etc.). I only get the answers without knowing the question. 2. the sortorder should only be used for order by, not matching. Since forms get edited now and then, the sortorder can get big (100 or more). Also, it must be grouped by question, or else multiple questions will be shown. Eg First name 2 entries would output 2x First name Hope you have a solution for this, I already can't thank you enough! — Ralph, Jun 02 '16 at 20:51
If it's too complex, maybe this is a good alternative (a combination of PHP and MySQL): Select the questions first with group by and instead of "is Question 1" replace it with the questions with a PHP while loop and then make the query? — Ralph, Jun 02 '16 at 20:53
Ok, so we need to define a row number in the inner query with two parameters (@id and @rank) and use the sort order just in the order by... As per your other note, since you have mixed data (the question names are different - i.e. the question name can be different for each position in the different forms) - you can't have them as column headings in the pivot. Instead, you can add them concatenated to the answer. This way you will see in the pivot the position, the question name and the answer but I'm not sure if this is of any help. See the updated query - hope it helps... — Koshera, Jun 02 '16 at 21:12

score 1 · Answer 2 · answered Jun 03 '16 at 12:21

Thank you all (especially Koshera!), with some tweaking I got it to work the way I wanted it.

This is the total code (raw, proof of concept;)). The function processQuery is a try / catch PDO execution with parameterized query (see the ? placeholder, the "raw" $i shouldn't be a problem?).

<div class="database-container">
<table border="0" cellspacing="0" cellpadding="7">
<tr>

<?php

// get all questions and group them so we only get unique questions
$getQuestions = processQuery("SELECT ansr_question FROM forms_123456 WHERE is_answer=1 and ansr_type != 'text' GROUP BY ansr_question ORDER BY ansr_sortorder ASC",$param,'fetch-raw',$server,$extra,$DBH);
unset($param);

// fetch all the questions
while($fetchQuestions = $getQuestions->fetch())

    {

        // save into array and generate the column questions
        $questions[] = $fetchQuestions["ansr_question"];
        $question_headers .= '<td height="19" nowrap style="background-color:#e9e9e9; font-weight:400;">'.trim(ucfirst($fetchQuestions["ansr_question"])).'</td>';

    }

// loop array
$num_questions = count($questions);
$num_questions_check = $num_questions -1;
for($i=0; $num_questions > $i; ++$i)

    {

    // prepare PDO params
    $param[] = $questions[$i];

    // use the count to perform if statement when last question has been reached
    $comma = ',';
    if($num_questions_check == $i)

        {
        $comma = '';
        }

    // generate the 2 dynamic parts of the query
    $sql_part1 .= "max(qq.question$i) as 'answer$i',";
    $sql_part2 .= "case when q.ansr_question = ? then ansr_answer else null end as question$i$comma ";

    }


// make it 1 query
$query = "select 
$sql_part1
ansr_recordid, 
ansr_type
FROM (
    SELECT
        ansr_recordid, 
        ansr_type,
        $sql_part2
    FROM(
        select 
            ansr_recordid, 
            ansr_question,
            ansr_answer,
            ansr_type,
            ansr_sortorder
            from forms_123456 where is_answer=1 order by ansr_recordid, ansr_sortorder 
            ) q
    )qq
GROUP BY ansr_recordid";

// lets try it!
$getAnswers = processQuery($query,$param,'fetch-raw',$server,$extra,$DBH);
unset($param);


// show questions and other data
echo '<td height="19" nowrap style="background-color:#e9e9e9; font-weight:600;">Status</td>
    <td height="19" nowrap style="background-color:#e9e9e9; font-weight:600;">Details</td>';

echo $question_headers;

echo '<td height="19" nowrap style="background-color:#e9e9e9; font-weight:600;">Record ID</td>
    <td height="19" nowrap style="background-color:#e9e9e9; font-weight:600;">Start Date</td>
    <td height="19" nowrap style="background-color:#e9e9e9; font-weight:600;">End Date</td>
</tr>';


// show values ----------------------------------------
$first = 1;
while($fetch = $getAnswers->fetch())

{

// change color per row
if($first == 0)

    {
    $first = 1;
    $bgcolor = '#f1f1f1';
    }
else
    {
    $first = 0;
    $bgcolor = '';
    }

echo '<tr>';

echo '<td height="19" nowrap style="background-color:'.$bgcolor.'; font-weight:400;">OK</td>
    <td height="19" nowrap style="background-color:'.$bgcolor.'; font-weight:400;">Bekijken</td>';

// loop the number of questions so we get the same amount of columns
for($i=0; $num_questions > $i; ++$i)

    {
    echo '<td height="19" nowrap style="background-color:'.$bgcolor.'; font-weight:400;">'.trim(ucfirst($fetch["answer$i"])).'</td>';
    }

echo '<td height="19" nowrap style="background-color:'.$bgcolor.'; font-weight:400;">'.$fetch["ansr_recordid"].'</td>
    <td height="19" nowrap style="background-color:'.$bgcolor.'; font-weight:400;">Start Date</td>
    <td height="19" nowrap style="background-color:'.$bgcolor.'; font-weight:400;">End Date</td>';

echo '</tr>';

}

?>

</table>
</div>

Pivot table in a specific way in MySQL

2 Answers2