数据准备
104399 1715131
104399 2105395
104399 1758844
104399 981085
104399 2444143
104399 1458638
104399 968412
104400 1609001
104400 2986088
104400 1795054
create table cookie4 (id string,value bigint) row format delimited fields terminated by "\t";
load data local inpath '/root/data/cookie4' overwrite into table cookie4;
select * from cookie4;
列转行
把相同id的value按照逗号转为一行
collect_list 不去重，collect_set 去重。 column的数据类型要求是string
select id,
concat_ws(',',collect_list(value)) as values
from cookie4
group by id;
+---------+--------------------------------------------------------+--+
| id | values |
+---------+--------------------------------------------------------+--+
| 104399 | 1715131,2105395,1758844,981085,2444143,1458638,968412 |
| 104400 | 1609001,2986088,1795054 |
+---------+--------------------------------------------------------+--+
行转列
create table cookie41 as
select id,
concat_ws(',',collect_list(value)) as values
from cookie4
group by id;
select * from cookie41;
+---------+--------------------------------------------------------+--+
| id | values |
+---------+--------------------------------------------------------+--+
| 104399 | 1715131,2105395,1758844,981085,2444143,1458638,968412 |
| 104400 | 1609001,2986088,1795054 |
+---------+--------------------------------------------------------+--+
# 列转行
drop table if exists cookie42;
create table cookie42 as
select id,values,value
from cookie41
lateral view explode(split(values,',')) as value;
+---------+--------------------------------------------------------+----------+--+
| id | values | value |
+---------+--------------------------------------------------------+----------+--+
| 104399 | 1715131,2105395,1758844,981085,2444143,1458638,968412 | 1715131 |
| 104399 | 1715131,2105395,1758844,981085,2444143,1458638,968412 | 2105395 |
| 104399 | 1715131,2105395,1758844,981085,2444143,1458638,968412 | 1758844 |
| 104399 | 1715131,2105395,1758844,981085,2444143,1458638,968412 | 981085 |
| 104399 | 1715131,2105395,1758844,981085,2444143,1458638,968412 | 2444143 |
| 104399 | 1715131,2105395,1758844,981085,2444143,1458638,968412 | 1458638 |
| 104399 | 1715131,2105395,1758844,981085,2444143,1458638,968412 | 968412 |
| 104400 | 1609001,2986088,1795054 | 1609001 |
| 104400 | 1609001,2986088,1795054 | 2986088 |
| 104400 | 1609001,2986088,1795054 | 1795054 |
+---------+--------------------------------------------------------+----------+--+
