行转列小结

2024-03-02 19:58

文章标签 小结转列

本文主要是介绍行转列小结，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

一、创建测试环境

1.创建测试数据

create table cc (student   nvarchar2(2),course    nvarchar2(2),score     int
);

2.插入测试数据

Insert into CC (STUDENT,COURSE,SCORE) values ('张三','语文',78);
Insert into CC (STUDENT,COURSE,SCORE) values ('张三','数学',87);
Insert into CC (STUDENT,COURSE,SCORE) values ('张三','英语',82);
Insert into CC (STUDENT,COURSE,SCORE) values ('张三','物理',90);
Insert into CC (STUDENT,COURSE,SCORE) values ('李四','语文',65);
Insert into CC (STUDENT,COURSE,SCORE) values ('李四','数学',77);
Insert into CC (STUDENT,COURSE,SCORE) values ('李四','英语',65);
Insert into CC (STUDENT,COURSE,SCORE) values ('李四','物理',85);
Insert into CC (STUDENT,COURSE,SCORE) values ('王五','语文',80);
Insert into CC (STUDENT,COURSE,SCORE) values ('王五','数学',90);
Insert into CC (STUDENT,COURSE,SCORE) values ('王五','英语',75);
Insert into CC (STUDENT,COURSE,SCORE) values ('王五','物理',91);
Insert into CC (STUDENT,COURSE,SCORE) values ('赵六','语文',93);
Insert into CC (STUDENT,COURSE,SCORE) values ('赵六','数学',76);
Insert into CC (STUDENT,COURSE,SCORE) values ('赵六','英语',86);
Insert into CC (STUDENT,COURSE,SCORE) values ('赵六','物理',77);

3.显示结果

二、行转列实现方法

1.使用vm_concat、pivot、decode函数实现

1）listagg：将满足条件的列值显示成一行，可以指定分割符，可以排序。

举例：

select STUDENT,listagg(SCORE,'|') within group (order by SCORE,STUDENT) B
from cc
group by STUDENT;

显示结果：

2） vm_concat：将满足条件的列值显示成一行。

缺点：显示结果顺序错乱。

之前采用这个方法进行合并，但是这是个非公开函数，而且12c后这个功能就被取消了，所以请用第一个方

法取代这个方法。

信息来源：http://blog.itpub.net/29867/viewspace-2123105/

替代方法： Tom 写过的 stragg
11g 可以用 listagg
9i/10g 可以用 sys_connect_by_path 来实现

举例：

SELECT STUDENT,WM_CONCAT(SCORE)FROM CC GROUP BY STUDENT;

显示结果：

改进：

select student,max(score)
from  (select student,wm_concat(score) over(partition by student order by score) as score from cc)
group by student;

显示结果：

3）pivot：对满足条件的A、B两列进行如下操作，将A列中指定的值生成为列名，将与之对应的B值生成为（新）列值。

缺点：只支持11g及以上版本。

举例：

select * from cc
PIVOT(max(score) FOR COURSE IN ('语文' AS 语文, '数学' AS 数学,'英语' as 英语 ,'物理' as 物理 ));

结果：

4）decode：判断字符串是否一样；

格式：DECODE(value,if1,then1,if2,then2,if3,then3,...,else)

举例：

 SELECT  student,MAX(DECODE(COURSE, '语文', SCORE)) as 语文,MAX(DECODE(COURSE, '数学', SCORE)) as 数学,MAX(DECODE(COURSE, '英语', SCORE)) as 英语,MAX(DECODE(COURSE, '物理', SCORE)) as 物理
FROM CC
GROUP BY student;

结果：

2.用case when实现

举例：

select STUDENT max(case when course='语文' then score  end) as 语文,max(case when course='数学' then score  end) as 数学,max(case when course='英语' then score  end) as 英语,max(case when course='物理' then score  end) as 物理
from cc 
group by STUDENT;

结果：