Hive行转列列转行

准备工作：

1. Hive建表：

CREATE TABLE IF NOT EXISTS A(
year STRING,
m1 STRING,
m2 STRING,
m3 STRING,
m4 STRING
)
ROW FORMAT delimited FIELDS TERMINATED BY '\t'
STORED AS textfile;

2. 插入数据：

INSERT INTO TABLE A VALUES('1991','1.1','1.2','1.3','1.4'),('1992','2.1','2.2','2.3','2.4');

需求：AB表互转
A表：

year	m1	m2	m3	m4
1991	1.1	1.2	1.3	1.4
1992	2.1	2.2	2.3	2.4

B表：

year	amount	month
1991	1.1	1
1991	1.2	2
1991	1.3	3
1991	1.4	4
1992	2.1	1
1992	2.2	2
1992	2.3	3
1992	2.4	4

A转B：

select year, amount,
row_number() over(partition by year) month
from A
lateral view explode(split(concat_ws(',',m1,m2,m3,m4),','))tmp as amount

B转A：

select year, amountList[0] m1,amountList[1] m2,amountList[2] m3
from(
  select year,
  collect_list(amount) amountList
  from B
  group by year
)tmp3;

为了方便测试，以下sql先A转B再转A：

select year, amountList[0] m1,amountList[1] m2,amountList[2] m3
from(
  select year,
  collect_list(amount) amountList
  from(
    select year,amount,
    row_number() over(partition by year) month
    from A
    lateral view explode(split(concat_ws(',',m1,m2,m3,m4),','))tmp as amount
  )tmp2
  group by year
)tmp3;