BigQuery-将多行连接为单行 [英] BigQuery - Concatenate multiple rows into a single row

问题描述

我有一个包含2列的BigQuery表:

I have a BigQuery table with 2 columns:

id|name
1|John
1|Tom
1|Bob
2|Jack
2|Tim

预期的输出:将按ID分组的串联名称

Expected output: Concatenate names grouped by id

id|Text
1|John,Tom,Bob
2|Jack,Tim

推荐答案

对于BigQuery标准SQL:

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

STRING_CONCAT中的可选ORDER BY name允许您获取如下所示的名称的排序列表

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     

对于旧版SQL

#legacySQL
SELECT 
  id, 
  GROUP_CONCAT(name) AS Text   
FROM yourTable
GROUP BY id  

如果您需要在此处输出排序列表，则可以在下面使用(以前-BigQuery Legacy SQL不能保证获得排序列表-但在我遇到的大多数实际情况下-可以使用)

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  

这篇关于BigQuery-将多行连接为单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！