BigQuery SQL:按键动态连接列中的所有行

Question

我有一个这样的表:

Customer    Plan/Date
A           free (20/01/2020-01/02/2020)
A           starter (01/02/2020-01/03/2020)
A           full (01/03/2020)
B           trial (02/03/2020-05/04/2020)
B           full (05/04/2020)
.....
.....

我需要按客户键在列计划/日期连接字符串并得到如下输出:

Customer    Plans
A          free (20/01/2020-01/02/2020), starter (01/02/2020-01/03/2020), full (01/03/2020)
B          trial (02/03/2020-05/04/2020), full (05/04/2020)

主要的挑战是每个客户的行数可能不同

谢谢你的帮助:)

Answer 1

如果你想要一个字符串，那么你想要string_agg():

select customer,
string_agg(plans, ', ')
from t
group by customer;

Answer 2

考虑如下-我认为在最终列表中保持适当的顺序是很重要的

select Customer,
string_agg(Plan_Date, ', ' order by parse_date('%d/%m/%Y', regexp_extract(Plan_Date, r'((.{10})'))) as Plans
from `project.dataset.table`
group by Customer