该案例主要是解决将相同的column放在同一row进行展示,不同的值形成不同的列
--from
test1.a test1.b test1.c
1 2 3
4 5 6
--to
typ val1 val2
a 1 4
b 2 5
c 3 6
--查询SQL,主要是将a列的所有值与'a'搭上关系,然后扁平化然后再聚合,实现思路不止一种,集思广益
select
typ,
collect_list(val)[0] as val1,
collect_list(val)[1] as val2
from(
select
split(word,'-')[0] as typ,
split(word,'-')[1] as val
from(
select
explode(array(a1,b1,c1)) as word
from (
select
concat('a-',a) as a1,
concat('b-',b) as b1,
concat('c-',c) as c1
from test1
) temp1
)temp2
) temp3 group by typ