presto-多行变一行

with t(id,a) as(
    select '1','a' union all
    select '2','c' union all
    select '2','d' )

select id,array_join(array_agg(a), ',') from t
group by id

array_agg将数据整合起来变成一个列表，array_join将列表以指定分隔符连接起来

结果

presto-一行变多行

with A(id,a) as(
    select '1','a' union all
    select '2','c' union all
    select '2','d' ),
    B(f1,f2) as (
        select 'a','1,2,3,4,5' union all
        select 'b','7,8,9'
    )

select f1,t1.ft from B
cross join unnest(split(f2,',')) t1(ft)

结果