Hive行转列列转行

准备工作：

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; 插入数据： 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表：

yearm1m2m3m419911.11.21.31.419922.12.22.32.4

B表：

yearamountmonth19911.1119911.2219911.3319911.4419922.1119922.2219922.3319922.44

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;