Oracle使用pivot和unpivot函数实现行列转换

admin Oracle

2023-12-04 0 389

一、需求说明

项目开发过程中涉及到oracle数据库的数据操作；但是需要将数据进行列的互转，通过查阅资料可知在oracle中有三种方式可以实现行列互转：

①使用decode 函数；

②使用case when 函数；

③使用pivot函数；

参考：Oracle中实现行列互转的方法分享

二、实现方法

我这里有一个数据表内容如下：

2.1、实现将上图内容转为行——decode函数

select
\”name\”,
max(decode(\”course\”, \’语文\’, \”score\”)) 语文,
max(decode(\”course\”, \’数学\’, \”score\”)) 数学,
max(decode(\”course\”, \’英语\’, \”score\”)) 英语,
sum(\”score\”) 总分
from \”grade\”
group by \”name\”;

2.2、实现将上图内容转为行——case when函数

select
\”name\”,
max(case when \”course\” = \’语文\’ then \”score\” end) 语文,
max(case when \”course\” = \’数学\’ then \”score\” end) 数学,
max(case when \”course\” = \’英语\’ then \”score\” end) 英语,
sum(\”score\”) 总分
from \”grade\”
group by \”name\”;

2.3、实现将上图内容转为行——pivot函数

pivot函数的语法：

pivot(聚合函数 for 列名 in(类型))select t.* from((select * from 原表名称)pivot( max(需转的列名称) for 需转的列名称 in(需转列对应的值1,需转列对应的值2,需转列对应的值3 ))t

SELECT t.*,(t.语文+t.数学+t.英语)总分 from ((SELECT \”name\”,\”course\”,\”score\” from \”grade\”)pivot
(
max(\”score\”)
for \”course\” in(\’语文\’ 语文,\’数学\’ 数学,\’英语\’ 英语)
))t ORDER BY \”name\”;

2.4、实现将上图内容转为列——unpivot函数

需要转的内容如下图：

unpivot函数的语法：

SELECT 列名称,需定义的列1名称,需定义的列2名称 from 表名称 unpivot (需定义的列2名称 for 需定义的列1名称 in(列2值1,列2值2,列2值3));

SELECT \”name\” 名字,course 课程,score 分数 from \”grade2\” unpivot (score for course in(\”chinese\”,\”math\”,\”english\”));

SELECT 名字,course 课程,score 分数 from (
SELECT \”name\” 名字,\”chinese\” 语文,\”math\” 数学,\”english\” 英语 from \”grade2\”) unpivot (score for course in(语文,数学,英语))