How to avoid duplicates in the STRING_AGG function

Question

My query is below:

select 
    u.Id,
    STRING_AGG(sf.Naziv, ', ') as 'Ustrojstvena jedinica',
    ISNULL(CONVERT(varchar(200), (STRING_AGG(TRIM(p.Naziv), ', ')), 121), '') 
    as 'Partner',

from Ugovor as u

        left join VezaUgovorPartner as vup
            on vup.UgovorId = u.Id AND vup.IsDeleted = 'false'
        left join [TEST_MaticniPodaci2].dbo.Partner as p
            on p.PartnerID = vup.PartnerId
        left join [dbo].[VezaUgovorUstrojstvenaJedinica] as vuu
            on vuu.UgovorId = u.Id
        left join [TEST_MaticniPodaci2].hcphs.SifZavod as sf
            on sf.Id = vuu.UstrojstvenaJedinicaId
        left join [dbo].[SifVrstaUgovora] as vu
            on u.VrstaUgovoraId = vu.Id

  group by u.Id, sf.Naziv

My problem is that I can have more sf.Naziv and also only one sf.Naziv so I have to check if there is one and then show only one result and if there is two or more to show more results. But for now the problem is when I have only one sf.Naziv, query returns two sf.Naziv with the same name because in first STRING_AGG i have more records about p.Naziv.

I have no idea how to implement DISTINCT into STRING_AGG function

Any other solutions are welcome, but I think it should work with DISTINCT function.

First get the distinct values, then in second query, perform the `STRING_AGG` or implement custom SQ CLR aggregate - they support distinct. — gotqn, Feb 26 '18 at 10:15
you may need to work out a subquery with nasif deduped and then do the select from it applying string_agg — Jayvee, Feb 26 '18 at 11:00
would your please add any example data and result you must wanted. — Fmanin, Mar 13 '18 at 08:56
Use one of the [solutions shown here](https://stackoverflow.com/questions/51646385/produce-distinct-values-in-string-agg). — Brent Arias, Aug 02 '18 at 18:03

score 6 · Answer 1 · answered Oct 05 '18 at 14:30

6

It looks like distinct won't work, so what you should do is put your whole query in a subquery, remove the duplicates there, then do STRING_AGG on the data that has no duplicates.

SELECT STRING_AGG(data)
FROM (
   SELECT DISTINCT FROM ...
)

answered Oct 05 '18 at 14:30

megabc123

200
1
5

score 3 · Answer 2 · answered Nov 11 '20 at 16:18

3

I like this format for distinct values: (d is required but you can use any variable name there)

SELECT STRING_AGG(LoadNumber, ',') as LoadNumbers FROM (SELECT DISTINCT LoadNumber FROM [ASN]) d

answered Nov 11 '20 at 16:18

JBoothUA

3,040
3
17
46

score 0 · Answer 3 · answered Jan 22 '23 at 08:58

A sample query to remove duplicates while using STRING_AGG().

WITH cte AS (
    SELECT DISTINCT product
    FROM activities
)
SELECT STRING_AGG(product, ',') products
FROM cte;

Or you can use the following query. The result is same -

SELECT STRING_AGG(product, ',') as products
from (
SELECT product
FROM Activities
GROUP BY product
) as _ ;

How to avoid duplicates in the STRING_AGG function

3 Answers3

Linked