oracle-sql与listAgg的连接,使用switchcase

我有一个带有多个联接和listagg的查询,如下所示;

select c.case_id caseId, c.last_name lastName, c.first_name firstName,  
 listagg(lkp_cs.descr,',') within group (order by lkp_cs.id) as caseStatus
 from cases c join lkP_alt lkp_alt
 on c.ATL = lkp_alt.id
 join  case_status cs
 ON cs.case_id = c.case_id
 join lkp_case_status lkp_cs
 on lkp_cs.id = cs.case_status_id
 join users u  
 ON 
    ((c.created_by is not null and c.assigned_to is null 
    and c.created_by = u.id) 
    or 
    (c.assigned_to is not null 
    and c.assigned_to = u.id)) 
 left outer join users_unit uu 
 on uu.user_id = u.id 
 and c.delete_date is null
 group by c.case_id, c.edipi, c.last_name, c.first_name
 

lkp_cs.descr->可以具有Active、Suspended、Renewed、Revoked、Closed、Deleted、Active A1、Active A3、Suspended-Ext、Closed-Ext等值。。。

一个案例可以有多个状态。但当其中一个状态为关闭时,它应该只返回closed。我可以提取值,但对于UI预览,我应该只显示关闭状态。 不过我不想在UI中这样做。我试过这个案子,但没有成功。有人能建议怎么做吗?

目前的输出是:

caseId  lastName    firstName   caseStatus                      
 100    AAA         BBB         Active, Active A1, Renewed
 101    CCC         DDD         Active, Active A1, Renewed, Closed
 102    CCC         DDD         Active, Active A1, Renewed, Closed Ext

这应该更改为:

caseId  lastName    firstName   caseStatus                      
 100    AAA         BBB         Active, Active A1, Renewed
 101    CCC         DDD         Closed
 102    CCC         DDD         Closed Ext