0

I need to use Google Cloud Platform to write my SQL codes. One problem I currently have writing the SQL query is, there is not much option to choose for concatenating strings.

For example, if my database is like this:

ID       Location
1        NY
1        TN
1        NC
2        FL
2        UK
3        MN

I hope to get a new table like this, which have a unique ID with a location column listed all the places the person went.

ID    Loc
1     NY, TN, NC
2     FL, UK
3     MN

I know in Mysql, you can use GROUP_CONCAT(see post: Can I concatenate multiple MySQL rows into one field?) or FOR XML PATH (see post: How to concatenate text from multiple rows into a single text string in SQL Server, but it seems like I can only use a limited function in the BigQuery.

Does anyone have some suggestions for solving this? Any thought is helpful.

Emily Zhang
  • 63
  • 5

1 Answers1

3

In BigQuery, you would use string_agg():

select id, string_agg(location, ', ' order by location) as locations
from t
group by id;

However, in BigQuery, you would normally use arrays for this:

select id, array_agg(location order by location)
from t
group by id;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Wow! That is really helpful. Thanks for the quick answer. I found exactly what I am looking for. I also see why your suggestion of array_agg, which return the result in a separate row but under the same ID name and a lot faster. Thanks! – Emily Zhang Sep 09 '21 at 19:34