hive中的列转行

1、建表

create table crisps_test.tmp_test1(
    col1 string,
    col2 string,
    col3 string,
    col4 string
)row format delimited fields terminated by '\t';

2、构建测试数据

insert into table crisps_test.tmp_test1
select 'a','b','1','0';
insert into table crisps_test.tmp_test1
select 'a','b','1','1';
insert into table crisps_test.tmp_test1
select 'a','b','2','1';
insert into table crisps_test.tmp_test1
select 'a','b','3','2';
insert into table crisps_test.tmp_test1
select 'c','d','1','0';
insert into table crisps_test.tmp_test1
select 'c','d','2','1';
insert into table crisps_test.tmp_test1
select 'c','d','3','2';

 

 

 3、执行转换

方法一：对要合并的数据不去重

set hive.execution.engine=spark;    -- 该步骤为设置hive执行引擎，如未安装spark可以不写
select
    col1
    ,col2
    ,concat_ws(",",collect_list(col3)) as col3_new
    ,concat_ws(",",collect_list(col4)) as col4_new
from crisps_test.tmp_test1
group by col1,col2

 

 

 方法二：对需要合并的数据进行去重

set hive.execution.engine=spark;    -- 该步骤为设置hive执行引擎，如未安装spark可以不写
select
    col1
    ,col2
    ,concat_ws(",",collect_set(col3)) as col3_new
    ,concat_ws(",",collect_set(col4)) as col4_new
from crisps_test.tmp_test1
group by col1,col2