一、列转行 (对某列拆分,形成新列)
使用函数:lateral view explode(split(column, ‘,’)) num
eg: 如表:t_row_to_column_tmp 数据如下,对tag列进行拆分
SQL代码:
select id,tag,tag_new
from t_row_to_column_tmp
lateral view explode(split(tag, ‘,’)) num as tag_new
where id=212022894;
二、行转列 (根据主键,对某列进行合并)
使用函数:concat_ws(‘,’,collect_set(column))
说明:collect_list 不去重,collect_set 去重。 column 的数据类型要求是 string
eg:如表:t_column_to_row ,根据id,对tag_new 进行合并
SQL代码1:
select id,
concat_ws(‘,’,collect_set(tag_new)) as tag_col
from t_column_to_row
group by id;
SQL代码2:
select id,
concat_ws(‘,’,collect_list(tag_new)) as tag_col
from t_column_to_row
group by id;