oracle中行列转换，max,listagg使用
详细讲一下Oracle的行列互换，
其中涉及到的知识点有：Decode，Case，wmsys.wm_concat，pivot以及unpivot
1 create table kecheng 2 ( 3 id NUMBER, 4 name VARCHAR2(20), 5 course VARCHAR2(20), 6 score NUMBER 7 ); 8 insert into kecheng (id, name, course, score) 9 values (1, '张三', '语文', 67); 10 insert into kecheng (id, name, course, score) 11 values (1, '张三', '数学', 76); 12 insert into kecheng (id, name, course, score) 13 values (1, '张三', '英语', 43); 14 insert into kecheng (id, name, course, score) 15 values (1, '张三', '历史', 56); 16 insert into kecheng (id, name, course, score) 17 values (1, '张三', '化学', 11); 18 insert into kecheng (id, name, course, score) 19 values (2, '李四', '语文', 54); 20 insert into kecheng (id, name, course, score) 21 values (2, '李四', '数学', 81); 22 insert into kecheng (id, name, course, score) 23 values (2, '李四', '英语', 64); 24 insert into kecheng (id, name, course, score) 25 values (2, '李四', '历史', 93); 26 insert into kecheng (id, name, course, score) 27 values (2, '李四', '化学', 27); 28 insert into kecheng (id, name, course, score) 29 values (3, '王五', '语文', 24); 30 insert into kecheng (id, name, course, score) 31 values (3, '王五', '数学', 25); 32 insert into kecheng (id, name, course, score) 33 values (3, '王五', '英语', 8); 34 insert into kecheng (id, name, course, score) 35 values (3, '王五', '历史', 45); 36 insert into kecheng (id, name, course, score) 37 values (3, '王五', '化学', 1); 38 commit;
一、行转列 ---固定
1.DECODE
SELECT ID,NAME, SUM(DECODE(course,'语文',score,0)) 语文,--这里使用max,min都可以 SUM(DECODE(course,'数学',score,0)) 数学, SUM(DECODE(course,'英语',score,0)) 英语, SUM(DECODE(course,'历史',score,0)) 历史, SUM(DECODE(course,'化学',score,0)) 化学 FROM kecheng GROUP BY ID ,NAME
2.Case方式
SELECT ID,NAME, MAX(CASE WHEN course='语文' THEN score ELSE 0 END) 语文, MAX(CASE WHEN course='数学' THEN score ELSE 0 END) 数学, MAX(CASE WHEN course='英语' THEN score ELSE 0 END) 英语, MAX(CASE WHEN course='历史' THEN score ELSE 0 END) 历史, MAX(CASE WHEN course='化学' THEN score ELSE 0 END) 化学 FROM kecheng GROUP BY ID ,NAME
3.wmsys.wm_concat
SELECT ID,NAME, wmsys.wm_concat(course || ':'||score) course FROM kecheng GROUP BY ID ,NAME;
这个函数需要转换一下，改成
SELECT ID,NAME, to_char(wmsys.wm_concat(course || ':'||score)） course FROM kecheng GROUP BY ID ,NAME;
4.pivot
SELECT * FROM kecheng PIVOT(SUM(score) FOR course IN('语文' , '数学' , '英语' , '历史' , '化学' ));
二、列转行
1.unpivot
select student,科目,成绩 from tmp_2 unpivot (成绩 for 科目 in (语文, 数学, 英语, 物理));
同样不使用unpivot也可以实现同样的效果，只是sql语句会很长，而且执行速度效率也没有前者高
