Concat data from different rows into one in BigQuery-CodePudding

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

CodePudding user response：

Does this work for you? if it works, please mark as answer

WITH DistinctValues AS(
SELECT DISTINCT
       V.DenormalisedData,
       SS.[Value]
FROM (VALUES((Select SUBSTRING(( SELECT ','   trim(Name) AS 'data()' FROM TableName FOR XML PATH('') ), 2 , 9999))))V(DenormalisedData)
     CROSS APPLY STRING_SPLIT(V.DenormalisedData,',') SS)


SELECT STRING_AGG(DV.[Value],',') AS RedenormalisedData
FROM DistinctValues DV
GROUP BY DenormalisedData;

CodePudding user response：

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