求一个sql

[hbgzg3006]

表 mytable
  a_id      p_id
  1           2     
  1           3    
  2           1    
  3           1    
  4           1   
  1           4
  5           2

我想找出每一个p_id对应的所有的a_id数据，然后我想取到一个合并的结果集
p_id 为1的a_id 有三个 2 3 4
p_id 为2的有两个  1 5
p_id为3的有一个1
p_id 为4的有一个1
这种表有办法完成么？谢谢。

[tangren]

--不知是否要这个
select a_id,p_id from mytable where p_id in (1,2,3,4);

[suiziguo]

10g或以上，用：

select a_id,replace(wm_concat(a_id),',',' ') from my_able;

[hbgzg3006]

[Quote=引用 1 楼 tangren 的回复:]

SQL code
--不知是否要这个
select a_id,p_id from mytable where p_id in (1,2,3,4);
[/Quote]
p_id会很大，呵呵，不只是1 2 3 4

[hbgzg3006]

[Quote=引用 2 楼 suiziguo 的回复:]

10g或以上，用：

select a_id,replace(wm_concat(a_id),',',' ') from my_able;
[/Quote]
Error at line 1
ORA-00937: 不是单组分组函数

[you_tube]

select a_id,replace(wm_concat(a_id),',','') result from table;

[tangren]

--9i,10g可用
with mytable as(
  select 1 a_id,2 p_id from dual union all  
  select 1 a_id,3 p_id from dual union all 
  select 2 a_id,1 p_id from dual union all 
  select 3 a_id,1 p_id from dual union all 
  select 4 a_id,1 p_id from dual union all
  select 1 a_id,4 p_id from dual union all
  select 5 a_id,2 p_id from dual)
SELECT a.p_id, ltrim(MAX(sys_connect_by_path(a_id, ',')), ',') a_id
  FROM (SELECT row_number() over(PARTITION BY t.p_id ORDER BY t.p_id) rn, t.* FROM mytable t) a
 START WITH rn = 1
CONNECT BY PRIOR rn = rn - 1 AND
           a.p_id = PRIOR a.p_id
 GROUP BY p_id
 ORDER BY p_id;

[tangren]

--10g用
select p_id,wm_concat(a_id) result from table group by p_id;

[you_tube]

测试数据:
select * from a;
        ID SNAME
---------- --------------------------------------------------
          2 yang
         4 Riddd
         2 ff
         4 Hlia
         4 Yreer
SYS_CONNECT_BY_PATH
适用范围：8i,9i,10g及以后版本
SELECT t.id id, MAX(substr(sys_connect_by_path(t.sname, ','), 2)) str
FROM (SELECT id, sname, row_number() over(PARTITION BY id ORDER BY sname) rn
FROM a) t
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
AND id = PRIOR id
GROUP BY t.id;

[hbgzg3006]

谢谢大家。呵呵。