oracle行列转换方法汇总

最新推荐文章于 2025-05-23 21:04:26 发布

转载最新推荐文章于 2025-05-23 21:04:26 发布 · 673 阅读

db 专栏收录该内容

3 篇文章

订阅专栏

本文详细介绍了如何使用Oracle数据库实现数据的行列转换，包括将多列数据转化为一行数据，以及将一行数据拆分为多列数据。通过多种SQL方法演示了从行转列到列转行的全过程，涵盖了WM_CONCAT、DECODE、CASE表达式、集合查询和INSERTALL操作等技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

第一部分：行转列

新建一个表：

CREATE TABLE HANG2LIE

( "ID" NUMBER,

"NAME" VARCHAR2(20),

"COURSE" VARCHAR2(20),

"SCORE" NUMBER

)

在表中插入如下的数据(为了锻炼下pl/sql所以这里费事写了一个PL/SQL程序):

declare

random_var number;

course_var varchar2(20);

begin

for i in 1..3 loop

for j in 1..5 loop

select mod(trunc(dbms_random.value(0,100)*190),100) into random_var from dual;

case

when j=1 then course_var:='语文';

when j=2 then course_var:='数学';

when j=3 then course_var:='英语';

when j=4 then course_var:='历史';

when j=5 then course_var:='化学';

end case;

insert into HANG2LIE(id,name,course,score) values(i,'name_' || i,course_var,random_var);

end loop;

end;

数据列出来如下：

ID NAME COUR SCORE

--- ------- ---- -----

1 name_1 语文 33

1 name_1 数学 63

1 name_1 英语 71

1 name_1 历史 68

1 name_1 化学 94

2 name_2 语文 85

2 name_2 数学 4

2 name_2 英语 98

2 name_2 历史 9

2 name_2 化学 12

3 name_3 语文 49

3 name_3 数学 96

3 name_3 英语 30

3 name_3 历史 60

3 name_3 化学 2

要实现的行转列的效果如下(或者类似的结果)：

ID NAME SCORES

--- ------- --------------------

1 name_1 33,63,71,94,68

2 name_2 85,4,98,12,9

3 name_3 49,2,60,96,30

1、通过Oracle数据库自带的wm_concat()函数来实现：

select id,name,wm_concat(score) scores

from HANG2LIE

group by id,name;

2、通过decode函数：

select id,name,sum(decode(course,'语文',score,null)) "语文",

sum(decode(course,'数学',score,null)) "数学",

sum(decode(course,'英语',score,null)) "英语",

sum(decode(course,'历史',score,null)) "历史",

sum(decode(course,'化学',score,null)) "化学"

from HANG2LIE

group by id,name;

得到的结果：

ID NAME 语文数学英语历史化学

--- ------- ---------- ---------- ---------- ---------- ----------

2 name_2 85 4 98 9 12

1 name_1 33 63 71 68 94

3 name_3 49 96 30 60 2

3、通过case表达式

select id,name,sum(case when course='语文' then score end) "语文",

sum(case when course='数学' then score end) "数学",

sum(case when course='英语' then score end) "英语",

sum(case when course='历史' then score end) "历史",

sum(case when course='化学' then score end) "化学"

from HANG2LIE

group by id,name;

得到的结果和第二种实际上是一样的，其实语句也是一样的，只不过把decode函数换成了case when表达式而已

第二部分：列转行

图省力呢，根据上面的表新建一个表：

create table lie2hang as

select id,name,sum(case when course='语文' then score end) Chinese,

sum(case when course='数学' then score end) Math,

sum(case when course='英语' then score end) English,

sum(case when course='历史' then score end) History,

sum(case when course='化学' then score end) Chemistry

from hang2lie

group by id,name;

结构如下：

ID NAME Chinese Math English History Chemistry

--- ------- ---------- ---------- ---------- ---------- ----------

2 name_2 85 4 98 9 12

1 name_1 33 63 71 68 94

3 name_3 49 96 30 60 2

我们要实现如下的查询效果：

ID NAME COUR SCORE

--- -------- ---- -----

2 name_2 语文 85

1 name_1 语文 33

3 name_3 语文 49

2 name_2 数学 4

1 name_1 数学 63

3 name_3 数学 96

2 name_2 英语 98

1 name_1 英语 71

3 name_3 英语 30

2 name_2 历史 9

1 name_1 历史 68

3 name_3 历史 60

2 name_2 化学 12

1 name_1 化学 94

3 name_3 化学 2

1、集合查询

实现的SQL语句：

select id,name,'语文' course,chinese score from lie2hang

union

select id,name,'数学' course,math score from lie2hang

union

select id,name,'英语' course,english score from lie2hang

union

select id,name,'历史' course,history score from lie2hang

union

select id,name,'化学' course,chemistry score from lie2hang;

这就是比较常见的列传行操作，主要原理是利用SQL的union集合查询。

2、insert all操作

语句如下：

create table lie2hang_result(

id number,

name varchar2(20),

course varchar2(20),

score number

);

insert all

into lie2hang_result(id,name,course,score) values(id,name,'语文',chinese)

into lie2hang_result(id,name,course,score) values(id,name,'数学',math)

into lie2hang_result(id,name,course,score) values(id,name,'英语',english)

into lie2hang_result(id,name,course,score) values(id,name,'历史',history)

into lie2hang_result(id,name,course,score) values(id,name,'化学',chemistry)

select id,name,chinese,math,english,history,chemistry from lie2hang;

这样的结果和第一种方法的相同，不过貌似不大好像起来用这个哈！

注：上文内容参考红黑联盟《Oracle行列转换小结》，原文地址：http://www.2cto.com/database/201108/100792.html