假设有如下的表结构：
| 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填充。
![http://mirrors.163.com/centos/7/os/x86_64/repodata/repomd.xml: [Errno 14] HTTP Error 500 - Internal Server Error
Trying other mirror.](https://linuxcpp.0voice.com/zb_users/upload/2023/05/202305162230567920271.png)
