![1b2a3da5b89b4ef374326fc689b17ebf.png](https://img-blog.csdnimg.cn/img_convert/1b2a3da5b89b4ef374326fc689b17ebf.png)
三个步骤:
- 输出行列互换的结构表
- 再用case...when...来输出数据
- 用group by及max输出最终结果
1。输出行列互换的结构表
SELECT 学号,'课程编号0001','课程编号0002','课程编号0003' from score;
![c6af7aad3e8ac753ae47d9bf18b31c7d.png](https://img-blog.csdnimg.cn/img_convert/c6af7aad3e8ac753ae47d9bf18b31c7d.png)
2。再用case...when...来输出数据
SELECT 学号,
(case when 课程号='0001' then 成绩 else 0 end) as '课程号0001',
(case when 课程号='0002' then 成绩 else 0 end) as '课程号0002',
(case when 课程号='0003' then 成绩 else 0 end) as '课程号0003'
from score;
![7f0c607294e7347e594f88f8b510b03a.png](https://img-blog.csdnimg.cn/img_convert/7f0c607294e7347e594f88f8b510b03a.png)
3.用group by及max输出最终结果
SELECT 学号,
max(case when 课程号='0001' then 成绩 else 0 end) as '课程号0001',
max(case when 课程号='0002' then 成绩 else 0 end) as '课程号0002',
max(case when 课程号='0003' then 成绩 else 0 end) as '课程号0003'
from score
group by 学号;
![63fcc988ac16845f041bfca20a2f2e35.png](https://img-blog.csdnimg.cn/img_convert/63fcc988ac16845f041bfca20a2f2e35.png)
拓展,列转行
![ab1a9bea92a1e39e6e143d98c8f44dc1.png](https://img-blog.csdnimg.cn/img_convert/ab1a9bea92a1e39e6e143d98c8f44dc1.png)
SELECT name,'english' as subject, english as score from a1
UNION ALL
SELECT name,'maths' as subject,maths as score from a1
UNION ALL
SELECT name,'music' as subject,music as score from a1;
参考:
行列互换问题,怎么办?送你一个万能模版
sql面试题:行列如何互换?
。