我正在尝试从多个表中获取数据，并寻找一种解决方案，将获取的结果中的行转换为列。
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 |
+----+---------+------+---------+
表格问题
+----+------------+
| 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 |
+----+-----------------+-----------+------------------+
要求的结果
+----+--------------+-------------+-------------+----------------------+
| 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|
+----+-----------------+-------------+-------------+----------------------+
尝试过
我尝试获取结果行并将其转换为列，但未成功。 我正在尝试这种格式 https://dba.stackexchange.com/questions/164711/how-to-pivot-rows-into-columns-mysql/164794
请您参考如下方法:
对于固定的问题列表，可以做条件聚合:
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
