1

I have a table in HIVE which looks like this

cust_1,month_1, f1,f2,f3 
cust_1,month_2, f2,f3,f4   
cust_2,month_1, f1,f5,f4

I would like to convert it in the following format

cust_1,month_1, f1
cust_1,month_1, f2   
cust_1,month_1, f3

....

How that is possible in HIVE?

nobody
  • 10,892
  • 8
  • 45
  • 63
HHH
  • 6,085
  • 20
  • 92
  • 164
  • This is pretty basic stuff. Hive doesn't have pivot/unpivot, so just `select cust, month, f1 union all select cust, month, f2...`. – Andrew Sep 06 '16 at 21:51
  • @Andrew `UNION ALL` to pivot data in Hive is inefficient. If you want to go wide to long, you create key value pairs and explode. Its outlined in the below answer. – o-90 Sep 08 '16 at 14:10
  • Possible duplicate of [Hive - Unpivot functionality in hive](http://stackoverflow.com/questions/32621970/hive-unpivot-functionality-in-hive) – o-90 Sep 08 '16 at 14:10

1 Answers1

2

you can use this sql:

select col1, col2, value 
     from orig_table lateral view explode(array(col3,col4,col5)) orig_table_alias as value;
aaronshan
  • 382
  • 3
  • 9