Oracle多表查询如何按case_id分组拼接多个caseStatus为逗号分隔字符串

我现有如下查询语句：

select c.case_id, c.last_name, c.first_name, lkp_alt.descr alt, lkp_cs.descr caseStatus, u.last_name || ','|| u.first_name createdBy,
             ul.last_name ||','||ul.first_name lastModifiedBy, c.LAST_MODIFIED_DATE
             from cases c left outer join lkP_alt_show_cause_authority lkp_alt
             on c.ATL_SHOW_CAUSE_AUTHORITY = lkp_alt.id
             left outer join cases_case_status cStatus
             on c.case_id = cStatus.case_id
             left outer join lkp_case_status lkp_cs
             on cStatus.case_status_id = lkp_cs.id
             left outer join users u
             on c.created_by = u.id
             left outer join users ul
             on c.LAST_MODIFIED_BY = ul.id;

当前查询输出结果如下：

case_id last_name first_name    alt     caseStatus  createdBy   lastModifiedBy  
1       lName   fName           MCCDC   Active      aaa         aaa             
1       lName   fName           MCCDC   Closed      aaa         aaa             
100     lName1  fName1          MCCDC   Closed      aaa         ,               
100     lName1  fName1          MCCDC   Active      aaa         ,               
16023   lName2  fName2          MCCDC   Closed      aaa         ,               
16023   lName2  fName2          MCCDC   Active      aaa         ,               
16025   lName3  fName3          MCCDC   Active      aaa         ,               
16025   lName3  fName3          MCCDC   Closed      aaa         ,               
16027   lName4  fName4          II MEF  JPAS        aaa         ,   
16027   lName4  fName4          II MEF  IGMC        aaa         ,   
16027   lName4  fName4          II MEF  Active      aaa         ,   
16029   lName5  fName5          MCCDC   Closed      aaa         ,               
16029   lName5  fName5          MCCDC   Active      aaa         ,               
16031   lName6  fName6          MCCDC   Closed      aaa         ,               
16031   lName6  fName6          MCCDC   Active      aaa         ,   

我希望实现的效果：同一个case_id对应多个caseStatus时，将多个状态值用逗号分隔拼接为单个字段，每个case_id仅返回一行数据，预期结果如下：

case_id last_name first_name    alt     caseStatus          createdBy   lastModifiedBy  
1       lName   fName           MCCDC   Active, Closed      aaa         aaa             
100     lName1  fName1          MCCDC   Active, Closed      aaa         ,               
16023   lName2  fName2          MCCDC   Active, Closed      aaa         ,               
16025   lName3  fName3          MCCDC   Active, Closed      aaa         ,               
16027   lName4  fName4          II MEF  JPAS, IGMC, Active  aaa         ,   
16029   lName5  fName5          MCCDC   Active, Closed      aaa         ,               
16031   lName6  fName6          MCCDC   Active, Closed      aaa         ,   

你需要用字符串聚合函数对caseStatus字段做分组拼接，同时按case_id和其他非聚合字段分组即可，不同数据库的语法差异如下：

1. MySQL / MariaDB #

使用GROUP_CONCAT函数，修改后的查询语句：

SELECT 
    c.case_id, 
    c.last_name, 
    c.first_name, 
    lkp_alt.descr alt, 
    GROUP_CONCAT(DISTINCT lkp_cs.descr ORDER BY lkp_cs.descr SEPARATOR ', ') caseStatus,
    CONCAT(u.last_name, ',', u.first_name) createdBy,
    CONCAT(ul.last_name, ',', ul.first_name) lastModifiedBy, 
    MAX(c.LAST_MODIFIED_DATE) LAST_MODIFIED_DATE
FROM cases c 
LEFT OUTER JOIN lkP_alt_show_cause_authority lkp_alt
    ON c.ATL_SHOW_CAUSE_AUTHORITY = lkp_alt.id
LEFT OUTER JOIN cases_case_status cStatus
    ON c.case_id = cStatus.case_id
LEFT OUTER JOIN lkp_case_status lkp_cs
    ON cStatus.case_status_id = lkp_cs.id
LEFT OUTER JOIN users u
    ON c.created_by = u.id
LEFT OUTER JOIN users ul
    ON c.LAST_MODIFIED_BY = ul.id
GROUP BY c.case_id, c.last_name, c.first_name, lkp_alt.descr, createdBy, lastModifiedBy;

注：加DISTINCT可避免重复的状态值，ORDER BY可以控制拼接的状态顺序，不需要可直接删除

2. PostgreSQL / SQL Server 2017及以上版本 #

使用STRING_AGG函数，替换上述查询中的聚合部分即可，其余分组逻辑不变：

STRING_AGG(DISTINCT lkp_cs.descr, ', ' ORDER BY lkp_cs.descr) caseStatus

3. Oracle 11g及以上版本 #

使用LISTAGG函数，替换聚合部分即可：

LISTAGG(lkp_cs.descr, ', ') WITHIN GROUP (ORDER BY lkp_cs.descr) caseStatus

如果需要去重，Oracle 19c及以上版本支持LISTAGG(DISTINCT ...)写法，低版本可先对子查询结果去重再做聚合。

内容的提问来源于stack exchange，提问作者Geek