我会在子查询中使用listagg():

select t1.*, xmlagg
from table1 t1 join
     (select name2, listagg(mother_name, ',') within group (order by mother_name) as xmlagg
      from table2 t2
      group by name2
     ) t2
     on t1.name1 = t2.name2;

上面的查询在加入之前进行汇总，因此可以使用t1.*.您也可以在加入后执行此操作:

The above query does the aggregation before the join, so it can use t1.*. You can also do it after the join:

select t1.name, listagg(mother_name, ',') within group (order by mother_name)
from table1 t1 join
     table2 t2
     on t1.name1 = t2.name2
group by t1.name;

这种形式使得向select中添加其他列变得更加困难，但是您可以按自己喜欢的方式进行汇总.

This form makes it harder to add additional columns to the select, but you can aggregate by anything you like.