convert a single row into 2 rows on impala/hive

Question

I have a huge table with millions of rows/IDs in the below format.

ID  date    bor1_key        bor1_fico04    bor1_fico08  bor2_key        bor2_fico04 bor2_fico08
A   202109  00657B187TH8    800            832          07T5O90Y009T    789         823

I need to convert this into the below format so that the values are in 2 rows as shown below.

ID  date    rownum  key             fico04  fico08
A   202109  2       07T5O90Y009T    789     823
A   202109  1       00657B187TH8    800     832

Can you please help me with an impala/hive query to help with this? Thanks a lot.

Answer 1

I think a way would be this one:

select
  d.ID, d.date, n.n as rownum,
  case 
    when n.n = 1 then d.bor1_key
    else d.bor2_key
  end as key,
  case 
    when n.n = 1 then d.bor1_fico04
    else d.bor2_fico04
  end as fico04,
  case 
    when n.n = 1 then d.bor1_fico08
    else d.bor2_fico08
  end as fico08    
from your_data d
  /* duplicate the results */
  left join (select 1 as n union all select 2 as n) n
    on 1=1