Concat data from different rows into one in BigQuery

Question

i want to concat my data from a particular column which is present in different rows.

The Data is something like this:

id | Name |
1 | Jack, John |
2 | John |
3 | John, Julie |
4 | Jack | 
5 | Jack, Julie |

I want the output as Jack, John, Julie. Every name should be unique.

I tried using string_agg(distinct Name), but the result is coming out as (Jack, John, John, John, Julie, Jack, Jack, Julie). How can i solve this issue and get desired result?

Thanks in advance

Answer 1

Use below

select string_agg(distinct trim(nm), ', ') as names
from your_table, unnest(split(name)) nm  

if applied to sample data in your question - output is