CODEWITHSUNDEEP
hive
Seeker90
Seeker90

Reputation: 895

Convert Column Names to Rows in Hive

I have a table in Hive. I have to convert the the column names to rows. The table that I have is as follows -

+---------+---------+---------+ |Table 1 | Table 2 | Table 3 | +---------+---------+---------+ | A | D | F | +---------+---------+---------+ | B | E | | +---------+---------+---------+ | C | | | +---------+---------+---------+

I need to convert it to a table with 2 columns. The first column will have the header -> Table name and values in the first column will be the column names of the old table. The final output should look like this - 

+-----------+--------+
|Table Name |   Val  |
+-----------+--------+
|  Table 1  |    A   |
+-----------+--------+
|  Table 1  |    B   |
+-----------+--------+
|  Table 1  |    C   |
+-----------+--------+
|  Table 2  |    D   |
+-----------+--------+
|  Table 2  |    E   |
+-----------+--------+
|  Table 3  |    F   |
+-----------+--------+

I am stuck. How do I get the needed output using Hive ?

Upvotes: 1

Views: 838

Answers (1)

leftjoin
leftjoin

Reputation: 38325

Use UNION ALL, provide static values for 1st column corresponding your column names:

CREATE TABLE new_table as
select * from 
(
select 'Table 1' as Table_Name, Table_1 as Val from your_table
UNION ALL
select 'Table 2' as Table_Name, Table_2 as Val from your_table
UNION ALL
select 'Table 3' as Table_Name, Table_3 as Val from your_table
) s where Val is not NULL
;

Upvotes: 1

Related Questions