BigQuery - Concatenate multiple rows into a single

问题:

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

回答1:

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  

回答2:

You can use GROUP_CONCAT

SELECT id, GROUP_CONCAT(name) AS Text FROM <dataset>.<table> GROUP BY name