how to split one row of result in to two rows in impala

Question

Below is the requirement and it needs to converted in SQL.

I will receive only one record from source but I need to verify below condition and split it into two rows in impala.

Expected values from source for final_columns is ('IC', 'OG', 'BK').

I need to check the below condition only if I received value 'BK' in final_columns.

if (final_columns in ('BK')) {
  split into rows based on below conditions
  when dr_type = 'IT' THEN dr_cid else dr_pid
  when cr_type = 'OR' THEN cr_cid else cr_pid
}
else {
  value as it is in source final columns
}

Answer 1

Could you please try this sql? I created dummy inline view to duplicate the data.

select 
    case when final_columns in ('BK') AND 
    case when dr_type = 'IT' THEN dr_cid 
    when dr_type <> 'IT' THEN dr_pid 
    when cr_type = 'OR' THEN cr_cid 
    when cr_type <> 'OR' THEN   cr_pid 
    END 
    else final_columns 
    end 
from 
    table,
    ( select 1 col union all select 1 union all select 2 ) dummy_record_creation
where 
    case when final_columns in ('BK') then 1 else 2 end = dummy_record_creation.col