Bruce Xiao 的程序生活

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::

ORACLE中表 test(a,b,c)有记录如下:
A                 B                      C
1                 XXX                   01
2                 YYY                   01
3                 KKK                   02
4                 III                   02
。。。。
把C相同的B根据序号A串联起来,即得到这样的结果
 B           C
XXXYYY       01
KKKIII       02

create table test(a int,b varchar2(100),c varchar2(100));
insert into test
select 1,'XXX','01' from dual union all 
select 2,'YYY','01' from dual union all
select 3,'KKK','02' from dual union all
select 4,'III','02' from dual;

select c,replace(substr(max(sys_connect_by_path(b, '|')), 2),'|','') b
from (select b, c, row_number() over(partition by c order by 1) rn
from test)
start with rn = 1
connect by rn - 1 = prior rn and c = prior c
group by c;

posted on 2007-11-30 09:35  Bruce Xiao  阅读(370)  评论(0编辑  收藏  举报