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.

Markova

First get the distinct values, then in second query, perform the STRING_AGG or implement custom SQ CLR aggregate - they support distinct. – Surakarta

you may need to work out a subquery with nasif deduped and then do the select from it applying string_agg – Elise

would your please add any example data and result you must wanted. – Massa

Use one of the solutions shown here. – Ahl

Answer 1

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 ...
)

Nadeen

Answer 2

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

Compositor

Answer 3

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 _ ;

Grope