oracle sql case返回结果集,sql结果集行列转换

SELECT行列转换

前几天有一个群友在群里面(190359237)有问到列转换成行的问题,今天终于有时间坐下来查阅资料做一个方法汇总,自己学习一下,也仅以此方式将这些分享给大家。

第一部分:行转列

新建一个表:

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 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;

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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值