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.
Advertisement
Answer
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