如何对STRING_AGG函数返回的OfferDetails列执行DISTINCT去重

SQL Server的STRING_AGG函数原生不支持直接拼接DISTINCT去重参数，你可以先对聚合的源数据做去重处理，再执行聚合操作，修改后的代码如下：

OUTER APPLY
(
    SELECT 
        STRING_AGG(CAST(OfferId AS NVARCHAR(MAX)), ';') AS OfferDetails, 
        STRING_AGG(CAST(ConferenceId AS NVARCHAR(MAX)), ';') AS CustomTransferDetails
    FROM (
        -- 先筛选去重得到唯一的OfferId、ConferenceId组合，避免聚合后出现重复值
        SELECT DISTINCT co.OfferId, ct.ConferenceId
        FROM ConferenceTransfer ct
        INNER JOIN ConferenceOffer co ON ct.ConferenceId = co.ConferenceId
        WHERE
            ct.ConferenceTransferTypeId = 1 AND
            ct.DateStarted IS NOT NULL AND
            ct.CallerUserId = @agentId AND
            ct.DateInitiated BETWEEN @dateFrom AND @dateTo
    ) AS DistinctTemp
) CallTransfered

如果需要对聚合后的结果排序，可以在STRING_AGG函数后追加WITHIN GROUP排序语句，示例：

STRING_AGG(CAST(OfferId AS NVARCHAR(MAX)), ';') WITHIN GROUP (ORDER BY OfferId ASC) AS OfferDetails

如果你需要对两个聚合字段做独立去重，分别针对每个字段单独写子查询做去重处理即可。

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