ROW_NUMBER() and SYS_CONNECT_BY_PATH in oracle 9i 合并列

合并列有很多方法，如聚焦函数、自定义函数利用游标分组、ROW_NUMBER() and SYS_CONNECT_BY_PATH 、还有10g后的wm_concat、及11G后新增函数..

下面验证ROW_NUMBER() and SYS_CONNECT_BY_PATH 用法

anbob@ANBOB> create table test_concat(groupno int,name varchar2(10));

Table created.

anbob@ANBOB> insert into test_concat values(1,'anbob');

1 row created.

anbob@ANBOB> insert into test_concat values(1,'weejar');

1 row created.

anbob@ANBOB> insert into test_concat values(2,'sina');

1 row created.

anbob@ANBOB> insert into test_concat values(2,'yahoo');

1 row created.

anbob@ANBOB> insert into test_concat values(2,'google');

1 row created.

anbob@ANBOB> commit;

Commit complete.

anbob@ANBOB> select * from test_concat;

   GROUPNO NAME
---------- ------------------------------
         1 anbob
         1 weejar
         2 sina
         2 yahoo
         2 google
         
anbob@ANBOB> select groupno,ltrim(max(SYS_CONNECT_BY_PATH(name,',')),',') conc
from (select groupno,name ,
 row_number() over(partition by groupno order by name) cur,
  row_number() over(partition by groupno order by name) -1 pre
  from test_concat)
  group by groupno
 connect by prior cur=pre  and groupno= prior groupno
start with cur=1
  ;

   GROUPNO CONC
---------- --------------------------------------------------
         1 anbob,weejar
         2 google,sina,yahoo

 ---10g 后有的方法
 anbob@ANBOB> select groupno,wm_concat(name) conc
  2  from test_concat 
  3  group by groupno;

———- ————————————————–
1 anbob,weejar
2 sina,google,yahoo