参考网址:http://m.jb51.net/article/90982.htm
https://www.bbsmax.com/A/Vx5MOwGzNr/
https://segmentfault.com/q/1010000009622091/a-1020000009656991
http://blog.itpub.net/17203031/viewspace-754807/
1、固定列数的行列转换
1)
如:
student | subject | grade |
---|---|---|
student1 | 语文 | 80 |
student1 | 数学 | 70 |
student1 | 英语 | 60 |
student2 | 语文 | 90 |
student2 | 数学 | 80 |
student2 | 英语 | 100 |
转换为:
语文 | 数学 | 英语 |
---|---|---|
student1 | 80 | 70 |
student2 | 90 | 80 |
语句如下:
select student,
sum(decode(subject,'语文', grade,null)) "语文",
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
from table
group by student;
2)
select student, wm_concat(grade) grades from table group by student;
结果
student | grades |
---|---|
student1 | 80 70 60 |
student2 | 90 80 100 |
3)