Concatenate distinct values in a group by

Question

I have data like this:

Group   Provider
A       ABC
A       DEF
B       DEF
B       HIJ

And I want to transform the data like this:

Group ProviderList
A      ABC, DEF
B      DEF, HIJ

I was trying something like this using a concat(select distinct...) but not sure if this is the best approach

SELECT distinct
  group, 
  CONCAT(select distinct provider from data)
FROM data 
GROUP BY 1

No need to do SELECT DISTINCT, your GROUP BY returns no duplicates. — jarlh, Feb 16 '22 at 21:02
if you write an answer out i can understand better and maybe mark as best answer @LaurenzAlbe — Hana, Feb 16 '22 at 21:29

score 3 · Accepted Answer · answered Feb 16 '22 at 21:43

3

What Laurenz meant with string_agg() is the following

SELECT
  group, 
  STRING_AGG(Provider,',') as ProviderList
FROM data 
GROUP BY 1

Optionally you could also use:

STRING_AGG(provider,',' order by Provider)

answered Feb 16 '22 at 21:43

Philippe

thank you for the answer! i figured it out prior but this works perfectly – Hana Feb 16 '22 at 22:00

1 Answers1