sql querty to get distinct values in string_agg function

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

Answer 1

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

Answer 2

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