Štítek: SQL

SQL STRING_AGG DISTINCT

Funkce STRING_AGG umožňuje vypsání hodnot ve sloupci v GROUP BY

STRING_AGG(Colour, ',')

Funkce však vypíše všechny položky tedy i duplicitní. MS SQL DISTINCT v STRING_AGG nepodporuje, na rozdíl od Postgresu.

string_agg(distinct (Colour), ', ')

Omezit položky na jedinečné lze vnořeným dotazem:

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

Více na: https://stackoverflow.com/questions/62055832/string-agg-with-distinct-without-sub-query