How to transpose rows into single column based on Customer ID index in Hive?

Question

I have a column with customer IDs and a bunch of columns (1 to 12) with date flags. I want to stack the non-zero date flags as dates in one column and repeat/replace the IDs corresponding to them in the ID column.

Input Data: 

ID  1       2       3       4       5       6       7       8       9        10     11      12
A   Jan-18  0       0       0       May-18  0       0       0       Sep-18   0      0       0
B   0       0       0       Apr-18  0       0       Aug-18  0       0        0      0       0

Expected Output:

ID  Date
A   Jan-18
A   May-18
A   Sep-18
B   Apr-18
B   Aug-18

Answer 1

Use union:

select id, col1  as `date`from tablename where col1 > '0'
union   
select id, col2  as `date`from tablename where col2 > '0'
union       
select id, col3  as `date`from tablename where col3 > '0'
union       
select id, col4  as `date`from tablename where col4 > '0'
union       
select id, col5  as `date`from tablename where col5 > '0'
union       
select id, col6  as `date`from tablename where col6 > '0'
union       
select id, col7  as `date`from tablename where col7 > '0'
union        
select id, col8  as `date`from tablename where col8 > '0'
union       
select id, col9  as `date`from tablename where col9 > '0'
union       
select id, col10 as `date`from tablename where col10 > '0'
union      
select id, col11 as `date`from tablename where col11 > '0'
union         
select id, col12 as `date`from tablename where col12 > '0'
;