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
Add a comment
|
2 Answers
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
1 Comment
Arsik36
This just helped me, happily upvoting
You can use GROUP_CONCAT
SELECT id, GROUP_CONCAT(name) AS Text FROM <dataset>.<table> GROUP BY id
1 Comment
Jaison
GROUP_CONCAT() for Legacy SQL and STRING_AGG() for Standard SQL