Hive行转列与列转行

行转列

定义：多行转多列或者多行转一列

原始数据如下：

姓名	科目	分数
小明	语文	90
小明	数学	98
小明	英语	95
小红	语文	92
小红	数学	93
小红	英语	97

drop table tmp.test0002;
create table tmp.test0002
(
    name  string comment '姓名',
    type  string comment '科目',
    score string comment '分数'
);
insert into table tmp.test0002
select '小明', '语文', '90';
insert into table tmp.test0002
select '小明', '数学', '98';
insert into table tmp.test0002
select '小明', '英语', '95';
insert into table tmp.test0002
select '小红', '语文', '92';
insert into table tmp.test0002
select '小红', '数学', '93';
insert into table tmp.test0002
select '小红', '英语', '97';

多行转多列示例一（利用case when函数）：

姓名	语文	数学	英语
小明	90	98	95
小红	92	93	97

select name,
       max(case when type = '语文' then score else 0 end),
       max(case when type = '数学' then score else 0 end),
       max(case when type = '英语' then score else 0 end)
from tmp.test0002
group by name
order by name;

多行转多列示例二（利用str_to_map函数）：

姓名	语文	数学	英语
小明	90	98	95
小红	92	93	97

select name
     ,info['语文'] as Chinese
     ,info['数学'] as Math
     ,info['英语'] as English
from (select name,str_to_map(concat_ws(',',collect_set(concat_ws(':',type,cast(score as string))))) as info
      from tmp.test0002
      group by name
     ) a;

多行转一列示例：

姓名	分数
小明	90,98,95
小红	92,93,97

select name,
       concat_ws(',', collect_list(score)) as score_list
from tmp.test0002
group by name
order by name;

列转行

定义：多列转多行或一列转多行

多列转多行示例一：

原始数据如下：

姓名	类型	分数
小明	语文,数学,英语	90,98,95
小红	语文,数学,英语	92,93,97

drop table tmp.test0003;
create table tmp.test0003
(
    name  string comment '姓名',
    type  string comment '学科',
    score string comment '分数'
);
insert into table tmp.test0003
select '小明', '语文,数学,英语', '90,98,95';
insert into table tmp.test0003
select '小红', '语文,数学,英语', '92,93,97';

转换后：

姓名	学科	分数
小明	语文	90
小明	数学	98
小明	英语	95
小红	语文	92
小红	数学	93
小红	英语	97

select name,
       type1,
       score1
from tmp.test0003
         lateral view
             explode(
                     str_to_map(
                             concat(
                                     '语文=', split(score, ',')[0],
                                     '&数学=', split(score, ',')[1],
                                     '&英语=', split(score, ',')[2]
                                 )
                         , '&', '=')
                 ) lateral_table as type1, score1
;

多列转多行示例二：

原始数据如下：

姓名	语文	数学	英语
小明	90	98	95

drop table tmp.test0003;
create table tmp.test0003
(
    name    string comment '姓名',
    chinese string comment '语文',
    math    string comment '数学',
    english string comment '英语'
);
insert into table tmp.test0003
select '小明', '90', '98', '95';

转换后：

姓名	学科	分数
小明	语文	90
小明	数学	98
小明	英语	95

select a.name
     , b.label
     , b.value
from (select *
      from tmp.test0003) a
         lateral view explode(map(
                 '语文', chinese
             , '数学', math
             , '英语', english
             )) b as label, value;

一列转多行示例

原始数据如下：

姓名	分数
小明	90,98,95
小红	92,93,97

drop table tmp.test0003;
create table tmp.test0003
(
    name  string comment '姓名',
    score string comment '分数'
);
insert into table tmp.test0003
select '小明', '90,98,95';
insert into table tmp.test0003
select '小红', '92,93,97';

转换后：

姓名	分数
小明	90
小明	98
小明	95
小红	92
小红	93
小红	97

select name,
       b.scores
from tmp.test0003
         lateral view explode(split(score, ',')) b as scores;