STRING_AGG with distinct without sub-query

Question

This is my data:

Code    SubCode    Colour     Fruit     Car     City     Name
A       A1         Red        Apple     Honda   Mel      John
A       A1         Green      Apple     Toyota  NYC      John
A       A1         Red        Banana    Honda   Lon      John
A       A1         Red        Banana    Opel    Mel      John
A       A2         ...
A       A2         ...
A       A3
A       A3

This is my sql:

SELECT Code, SubCode, STRING_AGG(Colour, ',') STRING_AGG(Fruit, ',') STRING_AGG(Car, ',') STRING_AGG(City, ',') STRING_AGG(Name, ',')
FROM myTable

I get this result:

Code    SubCode    Colour              Fruit                       Car                      City     Name
A       A1         Red,Green,Red,Red   Apple,Apple,Banana,Banan    Honda,Toyota,Honda,Opel  ...

Is there a way I get distinct values? Can I can create a sub-query with STRING_AGG?

Code    SubCode    Colour      Fruit           Car                   City     Name
A       A1         Red,Green   Apple,Banana    Honda,Toyota,Opel     ...

Answer 1

Alas, SQL Server's string_agg() currently does not support DISTINCT. So you would need multiple subqueries, like so:

select 
    code, 
    subcode, 
    (select string_agg(color, ',') from (select distinct color from mytable  t1 where t1.code = t.code and t1.subcode = t.subcode) t) colors,
    (select string_agg(fruit, ',') from (select distinct fruit from mytable  t1 where t1.code = t.code and t1.subcode = t.subcode) t) fruits,
    (select string_agg(car  , ',') from (select distinct car   from mytable  t1 where t1.code = t.code and t1.subcode = t.subcode) t) cars,
    (select string_agg(city , ',') from (select distinct city  from mytable  t1 where t1.code = t.code and t1.subcode = t.subcode) t) cities,
    (select string_agg(name , ',') from (select distinct name  from mytable  t1 where t1.code = t.code and t1.subcode = t.subcode) t) names
from mytable t
group by code, subcode

Note that your original query was missing a group by clause, because of which it was invalid SQL. I fixed that as well.