《oracle行转列列转行》由会员分享，可在线阅读，更多相关《oracle行转列列转行（9页珍藏版）》请在金锄头文库上搜索。
1、先来个简单的用法列转行 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,
2、0) 数学, sum(decode(km,英语,cj,0) 英语 from test1 group by name 姓名 语文 数学 英语 张三 80 86 75 李四 78 85 78 行转列with x as( select name, sum(decode(km,语文,CJ,0) 语文 , sum(decode(km,数学,cj,0) 数学, sum(decode(km,英语,cj,0) 英语 from test group by name) select name,decode(rn,1, 语文, 2, 数学, 3,英语) 课程, decode(rn, 1, 语文, 2, 数学, 3,
3、英语) 分数 from x, (select level rn from dual connect by 1=1 and level =3)(from 后面接两个表,是笛卡尔积)多行转字符串这个比较简单，用|或concat函数可以实现?123select concat(id,username) str from app_userselect id|username str from app_user字符串转多列实际上就是拆分字符串的问题，可以使用 substr、instr、regexp_substr函数方式 字符串转多行使用union all函数等方式wm_concat函数首先让我们来看看这个
4、神奇的函数wm_concat(列名),该函数可以把列值以,号分隔起来,并显示成一行,接下来上例子,看看这个神奇的函数如何应用准备测试数据?1234567create table test(id number,name varchar2(20);insert into test values(1,a);insert into test values(1,b);insert into test values(1,c);insert into test values(2,d);insert into test values(2,e);效果1 : 行转列 ，默认逗号隔开?1select wm_conc
5、at(name) name from test;效果2: 把结果里的逗号替换成|?1select replace(wm_concat(name),|) from test;效果3: 按ID分组合并name?1select id,wm_concat(name) name from test group by id;sql语句等同于下面的sql语句 ?123456789101112131415- 适用范围：8i,9i,10g及以后版本 （ MAX + DECODE ）select id, max(decode(rn, 1, name, null) | max(decode(rn, 2, , | n
6、ame, null) | max(decode(rn, 3, , | name, null) strfrom (select id,name,row_number() over(partition by id order by name) as rn from test) t group by id order by 1; - 适用范围：8i,9i,10g及以后版本 （ ROW_NUMBER + LEAD ）select id, str from (select id,row_number() over(partition by id order by name) as rn,name | l
7、ead(, | name, 1) over(partition by id order by name) |lead(, | name, 2) over(partition by id order by name) | lead(, | name, 3) over(partition by id order by name) as str from test) where rn = 1 order by 1;- 适用范围：10g及以后版本 （ MODEL ）select id, substr(str, 2) str from test model return updated rows par
8、tition by(id) dimension by(row_number() over(partition by id order by name) as rn) measures (cast(name as varchar2(20) as str) rules upsert iterate(3) until(presentv(striteration_number+2,1,0)=0) (str0 = str0 | , | striteration_number+1) order by 1; - 适用范围：8i,9i,10g及以后版本 （ MAX + DECODE ）select t.id
9、id,max(substr(sys_connect_by_path(t.name,),2) str from (select id, name, row_number() over(partition by id order by name) rn from test) tstart with rn = 1 connect by rn = prior rn + 1 and id = prior id group by t.id;懒人扩展用法:案例: 我要写一个视图,类似create or replace view as select 字段1,.字段50 from tablename ,基表有5
10、0多个字段,要是靠手工写太麻烦了,有没有什么简便的方法? 当然有了,看我如果应用wm_concat来让这个需求变简单，假设我的APP_USER表中有（id,username,password,age）4个字段。查询结果如下 ?12/* 这里的表名默认区分大小写 */select create or replace view as select | wm_concat(column_name) | from APP_USER sqlStr from user_tab_columns where table_name=APP_USER;利用系统表方式查询?1select * from user_t
11、ab_columnsOracle 11g 行列互换 pivot 和 unpivot 说明在Oracle 11g中，Oracle 又增加了2个查询：pivot（列转行） 和unpivot（行转列）参考：http:/ 一下，网上有一篇比较详细的文档：http:/www.oracle- 列转行测试数据 (id，类型名称，销售数量)，案例：根据水果的类型查询出一条数据显示出每种类型的销售数量。 ?123456789create table demo(id int,name varchar(20),nums int); - 创建表insert into demo values(1, 苹果, 1000);
12、insert into demo values(2, 苹果, 2000);insert into demo values(3, 苹果, 4000);insert into demo values(4, 橘子, 5000);insert into demo values(5, 橘子, 3000);insert into demo values(6, 葡萄, 3500);insert into demo values(7, 芒果, 4200);insert into demo values(8, 芒果, 5500);分组查询 （当然这是不符合查询一条数据的要求的）?1select name, su
13、m(nums) nums from demo group by name列转行查询?1select * from (select name, nums from demo) pivot (sum(nums) for name in (苹果 苹果, 橘子, 葡萄, 芒果);注意： pivot（聚合函数 for 列名 in（类型） ，其中 in() 中可以指定别名，in中还可以指定子查询，比如 select distinct code from customers当然也可以不使用pivot函数，等同于下列语句，只是代码比较长，容易理解?12select * from （select sum(nums) 苹果 from demo where name=苹果）,（select sum(nums) 橘子 f
