Oracle, 分组统计问题

[Linyufa]

工作中遇到这样一个问题：有没有办法通过一条SQL语句，根据如下原表得到如下查询结果
原始表:
clark, 10
king, 10
james, 10
smith, 20
scott, 20
ford, 30
adams, 30

查询结果
10, clark,king,james
20, smith,scott
30, ford,adams

我试着用SYS_CONNECT_BY_PATH，但好像实现不了。希望高人指点一二。

[hebo2005]

用WMSYS.WM_CONCAT
假设你两个字段分别是a,b
select b,WMSYS.WM_CONCAT(a) a
from table
group by b
出来的结果
b a
10 clark,king,james
20 smith,scott
30 ford,adams

[hebo2005]

如果你的原始表是一个a字段clark, 10 ，你想出来的结果为

10, clark,king,james

可以先分拆原来的字段，再用上面的语句，最后合并
select aa.a1||','||WMSYS.WM_CONCAT(a) aaa
(
select substr(a,instr(a,',',1)+1) a1,substr(a,1,instr(a,',',1)-1) a2
from table) aa
group by aa.a1
就能出来你想要的
aaa
10, clark,king,james
20, smith,scott
30, ford,adams

[jianghongping]

select deptno,
max(decode(seq,1,ename,null)) first,
max(decode(seq,2,ename,null)) second,
max(decode(seq,3,ename,null)) third
from ( select deptno,ename, row_number over
(partition by depton order by sal desc nulls last)
seq from emp
)
where seq<=3
group by deptno

[fxianxian]

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
 

[Linyufa]

1楼的提供的方法(WMSYS.WM_CONCAT)果然好用! C'est la Vie, 非常感谢您!
我没说清楚,我说的原表就是oracle自带的那个scott.emp表, 需要按部门分组统计人员,
人员之间用逗号分隔.

[xiangs]

我的ORACLE版本是9206的,所以没有这个函数可以用:(

[fq1798]

一楼的 我太感谢你了啊