假设有如下的表结构：
| id | name | course |
|---|---|---|
| 1 | Tom | Math |
| 2 | Tom | English |
| 3 | Tom | Science |
| 4 | John | Math |
| 5 | John | English |
| 6 | John | History |
我们想要将每个学生的课程转换成列，得到如下的结果：
| name | Math | English | Science | History |
|---|---|---|---|---|
| Tom | Math | English | Science | NULL |
| John | Math | English | NULL | History |
可以使用MySQL中的GROUP BY和CASE WHEN语句实现：
SELECT
name,
MAX(CASE WHEN course = 'Math' THEN course END) AS Math,
MAX(CASE WHEN course = 'English' THEN course END) AS English,
MAX(CASE WHEN course = 'Science' THEN course END) AS Science,
MAX(CASE WHEN course = 'History' THEN course END) AS History
FROM
students
GROUP BY
name;
以上SQL语句会将每个学生的课程按照名称转换成列，未选修的课程用NULL填充。
