再求一个查询办法。
不是必须要用一条SQL实现。用PLSQL实现也可以。

table1(id   varchar2(10),   name   varchar2(10))

id     name
1       aa
1       bb
1       cc
2       xx
3       yy
3       zz
...

想得到一个结果集如下：
id     names
1       aa+bb+cc
2       xx
3       yy+zz
...

请教了。

------解决方案--------------------
可能要先定义一个记录集的type,
create or replace type t_name as object
(
Id number(10),
Name varchar2(20),
);
create or replace type t_tab_names as table
of t_name;

create or replace procedure p_test (p_tTab_names out t_tab_names) as
i number;
flag number;
vtemp varchar2(100);
tname t_name;
begin
i:=0;
p_tTab_names:=new t_tab_names();
for v in (select id from table1 group by id) loop
i:=i+1;
flag:=0;
for vv in (select id,name from table1 where id=v.id) loop
if (flag=0) then
vtemp:=vv.name;
else
vtemp:=vtemp|| '+ '||vv.name;
end if;
flag:=1;
end loop;

tname.id:=v.id;
tname.name:=vtemp;
p_tTab_names.extend;
p_tTab_names(i):=tname;
end loop;

exception when others then
dbms_output.put_line(sqlerrm);

end p_test;
------解决方案--------------------

select distinct yy.id,
ltrim(first_value(yy.path) over(partition by yy.id order by yy.lev desc), '+ ') as name
from (
select zz.*,
sys_connect_by_path(zz.name, '+ ') as path,
level lev
from (
select tt.*,
tt.id || row_number() over(partition by tt.id order by tt.id)-1 as frontrn,
tt.id || row_number() over(partition by tt.id order by tt.id) as afterrn
from table1 tt
)zz
connect by prior zz.frontrn = zz.afterrn
)yy;


ID NAME
---------- --------------------------------------------
1 cc+bb+aa
2 xx
3 zz+yy

------解决方案--------------------
SQL> select * from a;

I NAM
- ---
1 aa
1 bb
1 cc
2 xx
2 yy
3 zz
3 mm

已选择7行。

SQL> select max(substr((sys_connect_by_path(name, ', ')),2)) col
2 from (
3 select id,name,
4 id+row_number() over( order by id) rn,
5 row_number() over(partition by id order by id) rn1
6 from a
7 )
8 start with rn1=1
9 connect by rn-1=prior rn
10 group by id
11 ;

COL
--------------------------------------------

aa,bb,cc
xx,yy
zz,mm

------解决方案--------------------
上一个麻烦了，用rownum更简单些

select max(substr((sys_connect_by_path(name, ', ')),2)) cola
from (
select id,name,
rownum rnum,
row_number() over(partition by id order by id) rn1
from a
)
start with rn1=1