/*本脚本可直接拷贝运行
上一次总结了三种字符串分组相加的方法，参看
http://www.itpub.net/606514.html
从大家的回复中得到第四种方法，所以整理了一下，做为一个续贴吧。
期待第五种、第六种......*/
create table test(id varchar2(10),mc varchar2(50));
insert into test values('1','11111');
insert into test values('1','22222');
insert into test values('2','11111');
insert into test values('2','22222');
insert into test values('3','11111');
insert into test values('3','22222');
insert into test values('3','33333');
commit;
select id,mc,row_number() over(partition by id order by id) rn_by_id,
row_number() over (order by id) + id rn from test;
/*
利用分析函数，构造两列，做为连接的条件：按照id分组，RN-1等于PRIOR RN作为条件连接。
ID MC RN_BY_ID RN
---------- -------------------------------------------------- ---------- ----------
1 11111 1 2
1 22222 2 3
2 11111 1 5
2 22222 2 6
3 11111 1 8
3 22222 2 9
3 33333 3 10
*/
select id,ltrim(max(sys_connect_by_path(mc,';')),';') add_mc from (
select id,mc,row_number() over(partition by id order by id) rn_by_id,
row_number() over (order by id) + id rn from test
)
start with rn_by_id = 1 connect by rn - 1 = prior rn
group by id
order by id;
/*
另用sys_connect_by_path函数实现字符串的连接，把最左边的分号去掉，即得到我们想要的结果
ID ADD_MC
---------- --------------------------------------------------------------------------------
1 11111;22222
2 11111;22222
3 11111;22222;33333
*/
select * from test;