Oracle行转列,列转行

先来个简单的用法

列转行

Create table test (name char(10),km char(10),cj int)

insert test values('张三','语文',80)

insert test values('张三','数学',86)

insert test values('张三','英语',75)

insert test values('李四','语文',78)

insert test values('李四','数学',85)

insert test values('李四','英语',78)

select name,

sum(decode(km,'语文',CJ,0)) 语文,

sum(decode(km,'数学',cj,0)) 数学,

sum(decode(km,'英语',cj,0)) 英语

from test1

group by name

姓名语文数学英语

张三80 86 75

李四78 85 78

行转列

with x as( selectname,

sum(decode(km,'语文',CJ,0)) 语文 ,

sum(decode(km,'数学',cj,0)) 数学,

sum(decode(km,'英语',cj,0)) 英语

fromtest

groupbyname)

selectname,decode(rn,1, '语文', 2, '数学', 3,'英语') 课程, decode(rn, 1, 语文, 2, 数学, 3,英语) 分数

from x, (selectlevel rn from dual connectby1=1andlevel<=3) (from 后面接两个表,是笛卡尔积)

多行转字符串

这个比较简单，用||或concat 函数可以实现

?

1 2 3 selectconcat(id,username) str fromapp_user

selectid||username str fromapp_user

字符串转多列

实际上就是拆分字符串的问题，可以使用 substr 、instr 、regexp_substr 函数方式

字符串转多行

使用union all 函数等方式

wm_concat 函数

首先让我们来看看这个神奇的函数wm_concat (列名),该函数可以把列值以","号分隔起来,并显示成一行,接下来上例子,看看这个神奇的函数如何应用准备测试数据

?

1 2 3 4 5 6 7 createtabletest(id number,namevarchar2(20));

insertintotest values(1,'a');

insertintotest values(1,'b');

insertintotest values(1,'c');

insertintotest values(2,'d');

insertintotest values(2,'e');

效果1 : 行转列 ，默认逗号隔开

?

1 s electwm_concat(name) namefromtest;

效果2: 把结果里的逗号替换成"|"

?

1 s electreplace(wm_concat(name),',','|') fromtest;

效果3: 按ID 分组合并name

?

1 s electid,wm_concat(name) namefromtest groupbyid;

sql 语句等同于下面的sql 语句

?

1 2 3 4 5 6 7 8 9 10 11 -------- 适用范围：8i,9i,10g 及以后版本 （ MAX + DECODE ） selectid, max(decode(rn, 1, name, null)) || max(decode(rn, 2, ','|| name, null)) || max(decode(rn, 3, ','|| name, null)) str from(selectid,name,row_number() over(partition byid orderbyname) asrn fromtest) t groupbyid orderby1;

-------- 适用范围：8i,9i,10g 及以后版本 （ ROW_NUMBER + LEAD ） selectid, str from(selectid,row_number() over(partition byid orderbyname) asrn,name|| lead(','|| name, 1) over(partition byid orderbyname) ||

lead(','|| name, 2) over(partition byid orderbyname) ||

12 13 14 15 lead(','|| name, 3) over(partition byid orderbyname) asstr fromtest) wherern = 1 orderby1;

-------- 适用范围：10g 及以后版本 （ MODEL ）

selectid, substr(str, 2) str fromtest model returnupdated

rowspartition by(id) dimension by(row_number() over(partition byid orderbyname) asrn)

measures (cast(nameasvarchar2(20)) asstr) rules upsert

iterate(3) until(presentv(str[iteration_number+2],1,0)=0) (str[0] = str[0] || ','|| str[iteration_number+1])

orderby1;

-------- 适用范围：8i,9i,10g 及以后版本 （ MAX + DECODE ） selectt.id id,max(substr(sys_connect_by_path(,','),2)) str from(selectid, name, row_number() over(partition byid orderbyname) rn fromtest) t

start withrn = 1 connectbyrn = priorrn + 1 andid = priorid groupbyt.id;

懒人扩展用法:

案例: 我要写一个视图,类似"create or replace view as select 字段1,...字段50 from tablename" ,基表有50多个字段,要是靠手工写太麻烦了,有没有什么简便的方法? 当然有了,看我如果应用wm_concat 来让这个需求变简单，假设我的APP_USER 表中有（id,username,password,age ）4个字段。查询结果如下

?

1 2 /** 这里的表名默认区分大小写 */

select'create or replace view as select '||

wm_concat(column_name) || ' from APP_USER'sqlStr

fromuser_tab_columns wheretable_name='APP_USER';

利用系统表方式查询

?