How to avoid duplicates in the STRING_AGG function SQL Server

Question

I was testing a query in SQL in which I need to concatenate values ​​in the form of a comma-separated list, and it works, I just have the problem of duplicate values.

This is the query:

SELECT t0.id_marcas                                AS CodMarca, 
       t0.nombremarcas                             AS NombreMarca, 
       t0.imagenmarcas, 
       (SELECT String_agg((t2.name), ', ')
        FROM exlcartu_devcit.store_to_cuisine t1 
               INNER JOIN exlcartu_devcit.cuisine t2 
                       ON t1.cuisine_id = t2.cuisine_id 
        WHERE  store_id = (SELECT TOP 1 store_id 
                           FROM   exlcartu_devcit.store 
                           WHERE  id_marcas = t0.id_marcas 
                                  AND status = 1)) AS Descripcion, 
       t0.logo, 
       t0.imagen, 
       (SELECT TOP 1 preparing_time 
        FROM   exlcartu_devcit.store 
        WHERE  id_marcas = t0.id_marcas 
               AND status = 1)                     AS Tiempo, 
       t0.orden, 
       (SELECT TOP 1 Avg(minimum_amount) 
        FROM   exlcartu_devcit.store_delivery_zone 
        WHERE  id_marcas = t0.id_marcas)           AS MontoMinimo 

FROM   exlcartu_devcit.[marcas] t0 

I thought the solution could be just adding a DISTINCT to the query to avoid repeated values ​​in this way ...

(SELECT STRING_AGG(DISTINCT (t2.name),  ', ') AS Descripcion

But apparently the STRING_AGG() function does not support it, any idea how to avoid repeated values?

Answer 1

Simplest way is just select from select, like this:

with dups as (select 1 as one union all select 1 as one)
select string_agg(one, ', ') from (select distinct one from dups) q;

vs original

with dups as (select 1 as one union all select 1 as one)
select string_agg(one, ', ') from dups;