hive 列转行 行转列

第一：列转行 (对某列拆分，一列拆多行)

lateral view explode(split(column, ',')) num

数据
2122 9,12
2123 12,15
2124 12,15
2125 1,12,15
建表
create table if not exists t_row_to_column_tmp(
id string,
tag string
)
row format delimited fields terminated by ' '
lines terminated by '\n'
stored as textfile
;
load data local inpath '/opt/wangyuqi/t_row_to_column_tmp.txt' into table t_row_to_column_tmp;
列转行
select id,tag,tag_new from t_row_to_column_tmp
lateral view explode(split(tag, ',')) num as tag_new
where id=2123;

第二：行转列 (根据主键，进行多行合并一列)

使用函数：concat_ws(',',collect_set(column))  

数据
2122 9
2122 12
2122 13
2122 1
2122 1
建表
create table if not exists t_column_to_row(
id string,
tag_new string
)
row format delimited fields terminated by ' '
lines terminated by '\n'
stored as textfile
;

load data local inpath '/opt/wangyuqi/t_column_to_row.txt' into table t_column_to_row;

select id,concat_ws(',',collect_set(tag_new)) as tag_col from t_column_to_row group by id;
select id,concat_ws(',',collect_list(tag_new)) as tag_col from t_column_to_row group by id;