从多个表中获取记录并将行转换为列mysql

Question

我正在尝试从多个表中提取数据，并寻找一种解决方案，将提取的结果行转换为列。

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| 
+----+-------------+ 

答案表格

+----+----------------+-----------+------------------+
| 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

- Esar-ul-haq Qasmi

---

搜索 mysql 行转列。 - P.Salmon

Answer 1

对于一个固定的问题列表，您可以进行条件聚合：

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

- GMB

---

动态问题列表？有解决方案吗？ - Esar-ul-haq Qasmi

@Esar-ul-haqQasmi：不能使用标准SQL。您需要使用动态SQL（即生成查询的查询，然后可以执行该查询），这似乎超出了您最初提出的问题的范围。 - GMB

请问您能指导我动态SQL应该包括什么吗？是否需要使用存储过程等？ - Esar-ul-haq Qasmi