I have a BigQuery table with 2 columns:
id|name
1|John
1|Tom
1|Bob
2|Jack
2|Tim
Expected output: Concatenate names grouped by id
id|Text
1|John,Tom,Bob
2|Jack,Tim
The Concatenation can be performed in BigQuery using the BigQuery CONCAT function or using the concatenation operator (“ || “).
There are two main ways to unite results with UNION in BigQuery which is: Comma-Delimited UNIONS in Legacy SQL and Standard SQL. Comma-Delimited UNIONS in Legacy SQL: With this method, merging data is very easy. All you need is a comma-separated list of the many tables included inside the FROM clause.
Limits when using a BigQuery data source AppSheet enforces the following limits for a BigQuery data source: Maximum of 100,000 rows - BigQuery datasets exceeding the maximum number of rows will be truncated.
For BigQuery Standard SQL:
#standardSQL
--WITH yourTable AS (
-- SELECT 1 AS id, 'John' AS name UNION ALL
-- SELECT 1, 'Tom' UNION ALL
-- SELECT 1, 'Bob' UNION ALL
-- SELECT 2, 'Jack' UNION ALL
-- SELECT 2, 'Tim'
--)
SELECT
id,
STRING_AGG(name ORDER BY name) AS Text
FROM yourTable
GROUP BY id
Optional ORDER BY name
within STRING_CONCAT
allows you to get out sorted list of names as below
id Text
1 Bob,John,Tom
2 Jack,Tim
For Legacy SQL
#legacySQL
SELECT
id,
GROUP_CONCAT(name) AS Text
FROM yourTable
GROUP BY id
If you would need to output sorted list here, you can use below (formally - it is not guaranteed by BigQuery Legacy SQL to get sorted list - but for most practical cases I had - it worked)
#legacySQL
SELECT
id,
GROUP_CONCAT(name) AS Text
FROM (
SELECT id, name
FROM yourTable
ORDER BY name
)
GROUP BY id
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With