Hive纵向表转横向表

问题描述：

有这样一张维度表(id name)，存放部门的id，与部门负责人

000	Liam	
000001	Noah	
000001001	Oliver	
000002	Emma
001	Elijah
001001	James
001002	Charlotte
001001001	William
001002001	Amelia
002	Benjamin
002001	Lucas
002001001	Theodore

需要合并相同部门的人，也就是相同前缀的id的多行记录，合并到一行记录上

期望结果(为空表示该条记录只有对应的二级部门，没有三级部门)

000	Liam	000002	Emma	NULL	NULL	
000	Liam	000001	Noah	000001001	Oliver	
001	Elijah	001001	James	001001001	William	
001	Elijah	001002	Charlotte	001002001	Amelia	
002	Benjamin	002001	Lucas	002001001	Theodore

实际案例：

建表

create table test.dept(id string, name string) row format delimited fields terminated by '\t';
load data inpath '/user/admin/dept.txt' into table dept;
select * from test.dept;

分析

因为源表是将同一部门id的数据存在了不多行，所以首先我们需要找出能关联的键，也就是下一级的前3位部门id等于上一级的部门id

子查询先筛选出各级部门id的数据，与上一级通过前三位关联

SQL

SELECT t1.id AS id_level1,
       t1.name AS name_level1,
       t2.id id_level12,
       t2.name AS name_level2,
       t3.id id_level3,
       t3.name AS name_level3
FROM
  (SELECT *
   FROM test.dept
   WHERE length(id)=3)t1
LEFT JOIN
  (SELECT *
   FROM test.dept
   WHERE length(id)=6)t2 ON t1.id=substr(t2.id,0,3)
LEFT JOIN
  (SELECT *
   FROM test.dept
   WHERE length(id)=9)t3 ON t2.id=substr(t3.id,0,6)
ORDER BY id_level3;

结果符合预期

id_level1	name_level1	id_level12	name_level2	id_level3	name_level3	
000	Liam	000002	Emma	NULL	NULL	
000	Liam	000001	Noah	000001001	Oliver	
001	Elijah	001001	James	001001001	William	
001	Elijah	001002	Charlotte	001002001	Amelia	
002	Benjamin	002001	Lucas	002001001	Theodore