求一个SQL问题

[churchatp1]

请教一句sql：
有表A(id,name)
B(id,A_id,name)
A的id是B的A_id的外键。
A内容：
A1 A
B内容：
1 A1 B1
2 A1 B2
3 A1 B3


如何用一条sql语句得到如下结果
A1 B1-B2-B3
也就是把B表对应同一条id的所有记录链接起来成为一个字段

谢谢

[jdsnhan]

一句sql不行吧,写个函数或过程来组合吧.

[sasacat]

select A_id, max(substr(sys_connect_by_path(name, '+'), 2)) name from (
select A_id, name, colc, lead(colc) over(partition by A_id order by colc) cold from (
select A_id, name, row_number() over(order by A_id, name) colc from B) )
start with cold is null
connect by prior colc=cold
group by A_id;