第一部分:行转列
新建一个表:
CREATE TABLE HANG2LIE
(
"ID" NUMBER,
"NAME" VARCHAR2(20),
"COURSE" VARCHAR2(20),
"SCORE" NUMBER
)
在表中插入如下的数据(为了锻炼下pl/sql所以这里费事写了一个PL/SQL程序):
declare
begin
for i in 1..3 loop
end loop;
end;
/
数据列出来如下:
--- ------- ---- -----
要实现的行转列的效果如下(或者类似的结果):
--- ------- --------------------
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;
得到的结果:
--- ------- ---------- ---------- ---------- ---------- ----------
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;
结构如下:
--- ------- ---------- ---------- ---------- ---------- ----------
我们要实现如下的查询效果:
--- -------- ---- -----
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