SQL分组子查询结合concat_ws()分隔合并结果集实现方法

原查询GROUP BY子句包含了te1.email字段，同一个收件人对应多个成本中心分析师（CCA）时，会被拆分为多条独立记录返回。

移除分组条件中的te1.email，仅按收件人维度分组，结合CONCAT_WS和字符串聚合函数将同组CCA值用分号拼接，实现单收件人单条记录返回。

适配SQL Server 2017及以上版本（支持CONCAT_WS/STRING_AGG） #

SELECT 
    te.email AS recipient_email,
    te.employee_name AS recipient_name,
    (
        SELECT CONVERT(VARCHAR, DATEADD(d, - 3, MAX(TRANSACTION_DATE)), 107)
        FROM [POT].[vw_Calendar]
        WHERE CURRENT_FISCAL_YEAR_FLAG = 'Y'
            AND CURRENT_FISCAL_QUARTER_FLAG = 'Y'
    ) AS accrual_deadline,
    'GR' AS emp_type,
    CONCAT_WS('; ', STRING_AGG(te1.email, '; ')) AS CCA
FROM [POT].[vw_POT_Open_PO] op 
LEFT JOIN POT.vw_Employee te 
    ON te.Employee_Number = op.Goods_Recipient_Emp_ID
    AND te.flag_Status = 'A'
JOIN POT.vw_Employee te1 
    ON te1.Employee_Number = op.pocostcenteranlystid 
LEFT JOIN [POT].[tbl_POT_Open_PO_Accrual_Reminder_History] opair 
    ON DATEPART(month, GETDATE()) + DATEPART(year, GETDATE()) = DATEPART(month, [Mail_Sent_On]) + DATEPART(year, [Mail_Sent_On])
    AND [Recipient_Email] = te.email
    AND opair.Flag_Active = 1
    AND [Reminder_Type] = 'Accrual_Input' 
WHERE te.email IS NOT NULL
    AND opair.Recipient_Email IS NULL
    AND te.Email IN ('siva@amat.com','mohan@amat.com')  
GROUP BY te.email, te.employee_name

低版本兼容写法（SQL Server 2016及以下，无内置STRING_AGG） #

如果数据库版本不支持高版本内置聚合函数，用FOR XML PATH配合STUFF实现同等拼接效果：

SELECT 
    te.email AS recipient_email,
    te.employee_name AS recipient_name,
    (
        SELECT CONVERT(VARCHAR, DATEADD(d, - 3, MAX(TRANSACTION_DATE)), 107)
        FROM [POT].[vw_Calendar]
        WHERE CURRENT_FISCAL_YEAR_FLAG = 'Y'
            AND CURRENT_FISCAL_QUARTER_FLAG = 'Y'
    ) AS accrual_deadline,
    'GR' AS emp_type,
    STUFF(
        (SELECT '; ' + t1.email
         FROM POT.vw_Employee t1
         WHERE t1.Employee_Number = op.pocostcenteranlystid
         FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'),
        1,2,''
    ) AS CCA
FROM [POT].[vw_POT_Open_PO] op 
LEFT JOIN POT.vw_Employee te 
    ON te.Employee_Number = op.Goods_Recipient_Emp_ID
    AND te.flag_Status = 'A'
LEFT JOIN [POT].[tbl_POT_Open_PO_Accrual_Reminder_History] opair 
    ON DATEPART(month, GETDATE()) + DATEPART(year, GETDATE()) = DATEPART(month, [Mail_Sent_On]) + DATEPART(year, [Mail_Sent_On])
    AND [Recipient_Email] = te.email
    AND opair.Flag_Active = 1
    AND [Reminder_Type] = 'Accrual_Input' 
WHERE te.email IS NOT NULL
    AND opair.Recipient_Email IS NULL
    AND te.Email IN ('siva@amat.com','mohan@amat.com')  
GROUP BY te.email, te.employee_name, op.pocostcenteranlystid

修改后查询返回结果完全匹配预期：

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