我有一个表A,看起来像这样:
| origin | food | category |
----------------------------------
| tree | apple | fruit |
| plant | tomato | fruit |
| plant | tomato | vegetable |
|........|..........|............|
| plant | tomato | vegetable |
我想浏览一下表格,对于所有combinations of origin and food
出现多次的表格,将其类别连接起来fruit + vegetable
并删除standalone versions
。因此上表为:
| origin | food | category |
-----------------------------------------
| tree | apple | fruit |
| plant | tomato | fruit + vegetable |
|........|..........|...................|
我只想使用standardSQL
。有什么想法吗?
谢谢
使用STRING_AGG(DISTINCT)
。
with data as (
select 'tree' as origin, 'apple' as food, 'fruit' as category union all
select 'plant', 'tomato', 'fruit' union all
select 'plant', 'tomato', 'vegetable' union all
select 'plant', 'tomato', 'vegetable'
)
select origin, food, string_agg(distinct category,' + ') as category
from data
group by 1,2
很好,谢谢!