none
sql querty to get distinct values in string_agg function RRS feed

  • Question

  • i have following query where i need 3rd column values comma separated which am getting it now   but i need distinct values how can i do it?

    WITH SelectedData As ( 
                SELECT Distinct
                    [R].[Name],
                    
                    COUNT(Distinct OFR.HeaderId) AS OfferCount,
    STRING_AGG(CAST([OFR].[HeaderId] AS NVARCHAR(MAX)), ',') as [Ids]
    FROM 
                   [Mapping].[OFR] OFR 
                   JOIN [Master].[R] R ON R.id = OFR.RId
                   JOIN [Transaction].[OH] OH ON OH.id = OFR.HeaderId
                   
                    WHERE
                    OH.PId = 3
                    AND [OFR].[IsActive] = 1
                    AND [R].[IsActive] = 1
                    AND [OH].[IsActive] = 1
    AND [U].Id =  173   AND [OFR].[RId] in (50) GROUP BY R.Name, OFR.RId ) 
    SELECT *,COUNT(1) OVER()  as [TotalRows]  from SelectedData 
    Order by [Name] OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

    Monday, September 10, 2018 7:48 AM

All replies

  • STRING_AGG doesnt support DISTINCT

    so you've to do FOR XML PATH way

    i.e

    WITH SelectedData As ( 
                SELECT
                    [R].[Name],
                    
                    of1.OfferCount,
    STUFF((SELECT ',' + CAST([m].[HeaderId] AS NVARCHAR(10)
    FROM (
    SELECT DISTINCT HeaderId
    FROM [Mapping].[OFR]
    WHERE RId = R.id
    AND [OFR].[IsActive] = 1 
    )m
    FOR XML PATH('')),1,1,'') as [Ids]
    FROM 
                    [Master].[R] R 
                   JOIN [Transaction].[OH] OH 
                   ON OH.id = OFR.HeaderId
                   JOIN (SELECT COUNT(Distinct OFR.HeaderId) AS OfferCount,
    			   Rid
    			   FROM [Mapping].[OFR]
    			   WHERE [OFR].[IsActive] = 1 
    			   GROUP BY Rid
    			   )of1
    			   ON of1.Rid = R.id
                    WHERE
                    OH.PId = 3
                    AND [R].[IsActive] = 1
                    AND [OH].[IsActive] = 1
    AND [U].Id =  173  
    GROUP BY R.id,R.Name ) 
    SELECT *,COUNT(1) OVER()  as [TotalRows]  
    from SelectedData 
    Order by [Name] OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
    


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Monday, September 10, 2018 8:50 AM
    Monday, September 10, 2018 8:47 AM
  • I had a similar issue, and solved it with a subquery.

    This may return duplicates in the Items aggregate if the same customer ordered the same item type more than once:

    SELECT
       CustomerID,
       STRING_AGG(ItemType, ';') WITHIN GROUP AS Items
    FROM
       OrdersTable
    GROUP BY
       CustomerID

    This only returns each item type once per customer, regardless of how many times that customer ordered that item type:

    SELECT
       CustomerID,
       STRING_AGG(ItemType, ';') WITHIN GROUP AS Items
    FROM
       (SELECT DISTINCT CustomerID, ItemType FROM OrdersTable) AS UniqueOrders
    GROUP BY
       CustomerID

    Friday, October 18, 2019 11:55 PM