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语句会很长，而且执行速度效率也没有前者高