CODEWITHSUNDEEP
google-bigquery
bartender
bartender

Reputation: 31

BigQuery get column as comma seperated values

Is there a Bigquery equivalent of SQLServer STUFF function to get a field as comma seperated values instead of multiple rows?

For example

**Table 1**
id  name
1   John
2   John
3   Tom
1   Harry
4   Harry
5   Harry

**Table 2**
id  group
1   group1
2   group2
3   group3
4   group4
5   group5

I want the result to be

name    group
John    group1,group2
Tom     group3
Harry   group1,group4,group5

Thanks in advance for the help

Upvotes: 2

Views: 4884

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59225

With standard SQL:

#standardSQL
SELECT name, STRING_AGG(DISTINCT `group` ORDER BY `group`) 
FROM (table or sub-select doing join)
GROUP BY name

Upvotes: 4

Related Questions