CODEWITHSUNDEEP
sqlsql-serverstringsubqueryaggregate-functions
asmgx
asmgx

Reputation: 7984

STRING_AGG with distinct without sub-query

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

Upvotes: 3

Views: 6902

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Sadly string_agg() does not support distinct. However, it is easy to emulate using row_number():

SELECT Code, SubCode,
       STRING_AGG(CASE WHEN seqnum_colour = 1 THEN Colour END, ','),
       STRING_AGG(CASE WHEN seqnum_fruit= 1 THEN Fruit END, ','),
       STRING_AGG(CASE WHEN seqnum_car = 1 THEN Car END, ','),
       STRING_AGG(CASE WHEN seqnum_city = 1 THEN City END, ','),
       STRING_AGG(CASE WHEN seqnum_name = 1 THEN Name END, ',')
FROM (SELECT t.*,
             ROW_NUMBER() OVER (PARTITION BY Code, SubCode, Colour ORDER BY Code) as seqnum_colour,
             ROW_NUMBER() OVER (PARTITION BY Code, SubCode, Fruit ORDER BY Code) as seqnum_fruit,
             ROW_NUMBER() OVER (PARTITION BY Code, SubCode, Car ORDER BY Code) as seqnum_car,
             ROW_NUMBER() OVER (PARTITION BY Code, SubCode, City ORDER BY Code) as seqnum_city,
             ROW_NUMBER() OVER (PARTITION BY Code, SubCode, Name ORDER BY Code) as seqnum_name
      FROM myTable t
     ) t
GROUP BY code, subcode;

Here is a db<>fiddle.

Upvotes: 4

GMB
GMB

Reputation: 222392

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.

Upvotes: 11

Related Questions