how to make a DISTINCT of STRING

I am trying to do a DISTINCT of the offerDetails column of the STRING_AGG function

OUTER APPLY
(
    SELECT 
        STRING_AGG(CAST(co.OfferId AS NVARCHAR(MAX)), ';') AS OfferDetails, 
        STRING_AGG(CAST(ct.ConferenceId AS NVARCHAR(MAX)), ';') AS CustomTransferDetails
    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
) CallTransfered

CodePudding user response：

I'm not aware of any native way of making string_agg distinct, but you can ignore all but the first instance of each value by using a subquery and row_number to identify the first instance, i.e.

OUTER APPLY
(
    SELECT 
        STRING_AGG(CAST(CASE WHEN c.RowNumber = 1 THEN c.OfferId END AS NVARCHAR(MAX)), ';') AS OfferDetails, 
        STRING_AGG(CAST(c.ConferenceId AS NVARCHAR(MAX)), ';') AS CustomTransferDetails
    FROM (  SELECT  co.OfferId,
                    ct.ConferenceId,
                    RowNumber = ROW_NUMBER() OVER(PARTITION BY co.OfferId ORDER BY 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 c
) CallTransfered

The key part being CASE WHEN c.RowNumber = 1 THEN c.OfferId END, so for anything other than the first instance, you are passing NULL, so the end result will only contain each OfferId once.

CodePudding user response：

You need to group by OfferId, then group again

OUTER APPLY
(
    SELECT 
        STRING_AGG(CAST(c.OfferId AS NVARCHAR(MAX)), ';') AS OfferDetails, 
        STRING_AGG(c.CustomTransferDetails, ';') AS CustomTransferDetails
    FROM (
        SELECT
            co.OfferId,
            STRING_AGG(CAST(ct.ConferenceId AS NVARCHAR(MAX)), ';') AS CustomTransferDetails
        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
        GROUP BY
            co.OfferId
    ) c
) CallTransfered

This obviously only works for one DISTINCT. If you need both to be distinct, you can re-split the values, distinct them and re-aggregate

OUTER APPLY
(
    SELECT 
        (
            SELECT
                STRING_AGG(s.value, ';')
            FROM (
                SELECT DISTINCT s.value
                FROM STRING_SPLIT(OfferDetails, ';') s
            ) s
        ) AS OfferDetails, 
        (
            SELECT
                STRING_AGG(s.value, ';')
            FROM (
                SELECT DISTINCT s.value
                FROM STRING_SPLIT(ConferenceId, ';') s
            ) s
        ) AS CustomTransferDetails
    FROM (
        SELECT
            STRING_AGG(CAST(co.OfferId AS NVARCHAR(MAX)), ';') AS OfferDetails, 
            STRING_AGG(CAST(ct.ConferenceId AS NVARCHAR(MAX)), ';') AS CustomTransferDetails
        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
        GROUP BY
            co.OfferId
    ) c
) CallTransfered