Fetch records from multiple tables and convert rows to columns mysql

user_4148431 0
Viewed 54

I am trying to fetch data from multiple tables and finding a solution to convert rows into columns from fetched results.

table_students, table_questions, table_answers

table_students

+----+---------+------+---------+
| id | exam_id| name| teacher  |
+----+---------+------+---------+
| 1  |123456  |John   |George  |
| 2  |6589974 |Nick   |Harry   |
| 3  |893258  |Michael|Thomas  |
+----+---------+------+---------+

table_questions

+----+------------+ 
| id | question   | 
+----+------------+ 
| 1  |Question one| 
| 2  |Question two| 
| 3  |Question three| 
+----+-------------+

table_answers

+----+----------------+-----------+------------------+
| id | exam_id        |question_id| answer           |
+----+----------------+-----------+------------------+
| 1  |123456           |1          | Answer given one|
| 2  |123456           |2          |Answer given two |
| 3  |123456           |3          |Answer given three|
| 4  |893258           |1          | Answer given two|
| 5  |893258           |2          |Answer given one |
| 6  |893258           |2          |Answer given one |
+----+-----------------+-----------+------------------+

Required results

+----+--------------+-------------+-------------+----------------------+
| id | exam_id      |Question one | Question two| Question three       |
+----+-----------------+-------------+-------------+----------------------+
| 1  |123456        |Answer given one |Answer given two |Answer given three|
| 2  |893258        |Answer given one | Answer given two|Answer given three|
+----+-----------------+-------------+-------------+----------------------+

Tried

I've tried to fetch and convert result rows to columns but not successful. I am trying this format https://dba.stackexchange.com/questions/164711/how-to-pivot-rows-into-columns-mysql/164794

mysql
1 Answers

For a fixed list of questions, you can do conditional aggregation:

select
    s.id,
    a.student_exam_id,
    max(case when q.question = 'Question one'   then a.answer end) `Question one`,
    max(case when q.question = 'Question two'   then a.answer end) `Question two`,
    max(case when q.question = 'Question three' then a.answer end) `Question three`
from 
    table_students s
    inner join table_answers a on a.student_exam_id = s.exam_id
    inner join table questions q on q.id = a.question_id
group by 
    s.id,
    a.student_exam_id
user_106767160
Related