HiveSQL行列转换题

Question

表原来的结构和内容

期望的结构和内容

麻烦各位看一下，在hivesql中运行，用了explode（split（））进行分割了，然后该怎么办啊。

Answer 1

--测试数据
create table test_20220220(id int,col string);
insert into test_20220220 values (1,'a,b,c');
insert into test_20220220 values (2,'1-2-3');
insert into test_20220220 values (3,'A/B/C');

--查询sql
select COLLECT_LIST(g[1])[0] as l1,
COLLECT_LIST(g[2])[0] as l2,
COLLECT_LIST(g[3])[0] as l3
from 
(
select new_line,map(id,val) g from 
(SELECT id,  val,row_number() over(partition by id order by val) new_line
FROM test_20220220  lateral view explode(split(col,',|-|/')) lv as val) as t
) as t2
group by new_line;

添加一个辅助列id,主要为了确定行的顺序,要不然无法知道哪行是第一行,哪行是第二行