17,081
社区成员
发帖
与我相关
我的任务
分享
SQL> with a as (select 'clark' name,10 id from dual
2 union
3 select 'king' name,10 id from dual
4 union
5 select 'james' name,10 id from dual
6 union
7 select 'simth' name,20 id from dual
8 union
9 select 'scott' name,20 id from dual
10 union
11 select 'ford' name,30 id from dual
12 union
13 select 'adams' name,30 id from dual
14 )
15 select id,max(sys_connect_by_path(name,',')) name
16 from (select a.*,row_number()over(partition by id order by id) rn
17 from a)
18 group by id
19 start with rn=1
20 connect by rn-1=prior rn and id=prior id
21 order by id
22 /
ID NAME
---------- --------------------------------------------------------------------------------
10 ,james,king,clark
20 ,simth,scott
30 ,ford,adams