《Oracle行转列,列转行.docxVIP

先来个简单的用法列转行 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( 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,英语) 分数 from x, (select level rn from dual connect by 1=1 and level =3)(from 后面接两个表,是笛卡尔积)多行转字符串这个比较简单，用||或concat函数可以实现?123select concat(id,username) str from app_user?select id||username str from app_user字符串转多列实际上就是拆分字符串的问题，可以使用 substr、instr、regexp_substr函数方式 字符串转多行?使用union all函数等方式wm_concat函数首先让我们来看看这个神奇的函数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_concat(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, , || name, null)) || max(decode(rn, 3, , || name, null)) str?from (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 || lead(, || name, 1) over(partition by id order by name) ||?lead(, || n