Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

BigQuery - Concatenate multiple rows into a single row

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
like image 990
Jaison Avatar asked Feb 02 '17 08:02

Jaison


People also ask

How do I concatenate rows in BigQuery?

The Concatenation can be performed in BigQuery using the BigQuery CONCAT function or using the concatenation operator (“ || “).

How do you use a union in BigQuery?

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.

How many rows can BigQuery handle?

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.


1 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  
like image 102
Mikhail Berlyant Avatar answered Oct 18 '22 20:10

Mikhail Berlyant