Split single row columns into multiple columns in Hive

1
votes

I have a Hive table like this

ID1 Name1 ID2 Name2 ID3 Name3....
1   ABC   2   MNP   3   XYZ
11  LMP   12  PLL   13  UIP

This table may have any no. of columns pair(i.e. ID and Name)

I need to convert the above table into a new Hive table having only 2 columns ID and Name like below

ID  Name
1   ABC
2   MNP
3   XYZ
11  LMP
12  PLL
13  UIP

Please suggest how can I achieve this in Hive.

Thanks

1
sqlhadoophivehiveql

1 Answers

2
votes
select  explode(map(*)) as (id,name)
from    mytable
;

+----+------+
| id | name |
+----+------+
|  1 | ABC  |
|  2 | MNP  |
|  3 | XYZ  |
| 11 | LMP  |
| 12 | PLL  |
| 13 | UIP  |
+----+------+