oracle 聚合函数 结果集为一对多，将多个行合并，并用逗号分隔

select t1.cust_name 客户名称,
t3.offer_name 套餐名称,
t4.os_status  使用状态,
t5.acct_name 账户名称
from cm_cust  t1 
join ur_user   t2
on t1.cust_id = t2.cust_id
join ur_user_offer  t3
on t3.user_id = t2.user_id
join ur_user_status t4
on t4.user_id = t2.user_id
join ac_acct t5
on t5.cust_id = t1.cust_id
where t1.state = 'Y'
and t2.user_state = '1'

sql 的结果集为一对多的关系，想要将套餐名称，使用状态合并，并且用逗号分隔

可以使用LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)函数

示例代码：

select t1.cust_name 客户名称,
listagg (t3.offer_name,',') within group (order by t3.offer_name) 套餐名称
 ,listagg (t4.os_status,',')within group (order by t4.os_status) 使用状态
from cm_cust  t1 
join ur_user   t2
on t1.cust_id = t2.cust_id
join ur_user_offer  t3  
on t3.user_id = t2.user_id
join ur_user_status t4
on t4.user_id = t2.user_id
where t1.state = 'Y'
and t2.user_state = '1'
group by t1.cust_name

运行结果