with t1 as (select 1 as student, "math" as ck, 3 as sub union all select 3 as student, "math" as ck,3 as sub
union all select 5 as student, "math2" as ck , 4 as sub)
select student,
max(ck_pv) as math,
max(ck2_pv) as math2
from (
select student,
if(ck = "math", sub, 0 ) as ck_pv,
if(ck = "math2", sub,0 ) as ck2_pv
from t1
)
group by student
多行转移一列 :concat(a,',',b) collect_set/collect_list groupby