2021-05-14

hive 多行转多列

+----------------------------------+------------------------------------------+------------------------------------------+
| test_tpl_type_lvl2_f.album_type  | test_tpl_type_lvl2_f.lvl1_tpl_type_desc  | test_tpl_type_lvl2_f.lvl2_tpl_type_desc  |
+----------------------------------+------------------------------------------+------------------------------------------+
| a                                | 影集                                     | 送祝福                                   |
| b                                | 影集                                     | MV                                       |
| c                                | 影集                                     | 极速影集                                 |
| d                                | 影集                                     | 图文                                     |
| e                                | 视频                                     | 视频剪辑                                 |
| f                                | 影集                                     | 经典模板                                 |
| g                                | 视频                                     | 发视频                                   |
| h                                | 未知                                     | 未知                                     |
| i                                | 其他                                     | 其他                                     |
+----------------------------------+------------------------------------------+------------------------------------------+

+---------------------+--------+------+---------+-------+---------+---------+--------+-------+-----+
|        title        |  a     |  b   |   c     |  d    |   e     |   f     |  g     |  h    |  i  |
+---------------------+--------+------+---------+-------+---------+---------+--------+-------+-----+
| lvl1_tpl_type_desc  | 影集   | 影集 | 影集    | 影集  | 视频    | 影集    | 视频   | 未知  | 其他|
| lvl2_tpl_type_desc  | 送祝福 | MV   | 极速影集| 图文  | 视频剪辑| 经典模板| 发视频 | 未知  | 其他|
+---------------------+--------+------+---------+-------+-------  +---------+--------+-------+-----+

 

 

select
title,
max(case when album_type = 'a' then value else '' end) as a,
max(case when album_type = 'b' then value else '' end) as b,
max(case when album_type = 'c' then value else '' end) as c,
max(case when album_type = 'd' then value else '' end) as d,
max(case when album_type = 'e' then value else '' end) as e,
max(case when album_type = 'f' then value else '' end) as f,
max(case when album_type = 'g' then value else '' end) as g,
max(case when album_type = 'h' then value else '' end) as h,
max(case when album_type = 'i' then value else '' end) as i
from
(select
album_type,
c.a as title,
c.b as value
from test_tpl_type_lvl2_f
lateral view explode(map(
'lvl1_tpl_type_desc',lvl1_tpl_type_desc,
'lvl2_tpl_type_desc',lvl2_tpl_type_desc))c as a,b)t1
group by title;