Whenever I want to pivot a table in Hive, I collect
key:value pairs to a map and then reference each key in the next level, creating new columns. This is the opposite of that.
select a.userid, y.new_id from ( select new_id, fruit_name, fruit_code from ( select new_id, map("apple_id", apple_id , "mango_id", mango_id , "grape_id", grape_id , "peach_id", peach_id) as fruit_map from table_2 ) x lateral view explode(fruit_map) exptbl1 as fruit_name, fruit_code ) y join table_A a on (y.fruit_code=a.property_id)
0001 N456098 0031 N002345 0008 N129087 00013 N109876