唉，sql server的 string_agg() 目前不支持 DISTINCT . 因此需要多个子查询，例如：

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

请注意，原始查询缺少 group by 子句，因此它是无效的sql。我也修好了。

悲哀地 string_agg() 不支持 distinct . 但是，使用 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;

这是一把小提琴。