不知道该起个怎样的标题，讲课时一位学员提出了这样一个需求，搜了一下可以通过wmsys.wm_concat来实现，但是客户使用的是9i，于是在群里的一个哥们的帮助下，通过树状sql结合分析函数实现了客户的要求,不过客户反应看不懂，后来我还是用cursor笨办法来实现一下：

SQL> select * from t;

ID NAME AGE
---------- ---------- ----------
2 b 30
2 a 30
1 a 30
1 b 30

SQL> select id,name_path from (select id,name_path,
2 row_number() over(partition by id order by id,curr_level desc) name_path_rank
3 from (select id,name,rank,level as curr_level,
4 ltrim(sys_connect_by_path(name,','),',') name_path from (
5 select id,name,row_number() over(partition by id order by id,name) rank
6 from t order by id,name) connect by id = prior id and rank-1 = prior rank))
7 where name_path_rank=1;

ID NAME_PATH
---------- ----------
1 a,b
2 a,b

SQL>

--==================================

10g的话oracle提供了函数：wmsys.wm_concat可以很容易的实现：

SQL> select * from t;

ID NAME AGE
---------- ---------- ----------
2 b 30
2 a 30
1 a 30
1 b 30

SQL> select id,wmsys.wm_concat(name) a from t group by id;

ID A
---------- ----------
1 a,b
2 b,a

SQL> select id,wmsys.wm_concat(name) a from (select id,name from t order by name) group by id;

ID A
---------- ----------
1 a,b
2 a,b

SQL>

--=============================

SQL> declare
2 v_string varchar2(20) := null;
3 begin
4 for i in (select distinct id from t) loop
5 v_string := null;
6 for j in (select name from t where id=i.id order by name) loop
7 v_string := v_string||j.name||',';
8 end loop;
9 dbms_output.put_line(substr(i.id||' '||v_string,1,length(i.id||' '||v_string) - 1));
10 end loop;
11 end;
12 /
1 a,b
2 a,b

PL/SQL procedure successfully completed.

SQL>