There is a need in the SQL code below will also get the table name from which the column was fetched to maintain a lineage for analysis at later point. I need suggestion to implement such a SQL:
select
COALESCE(t1.col1,t2.col1,t3.col1) new_col1,
COALESCE(t1.col2,t2.col2,t3.col2) new_col2,
COALESCE(t1.col3,t2.col3,t3.col3) new_col3
from
table1 t1
left join table2 t2 on t1.id = t2.id
left join table3 t3 on t1.id = t3.id
In the result, I need to get an output similar to this:
new_col1 new_col2 new_col3 new_col1_source new_col2_source new_col3_source
val1 val2 val3 table1 table1 table3
in the above result, the last 3 columns should provide the table names from which the first 3 columns were fetched from.
CodePudding user response:
You can do this:
select
COALESCE(t1.col1,t2.col1,t3.col1) new_col1,
COALESCE(t1.col2,t2.col2,t3.col2) new_col2,
COALESCE(t1.col3,t2.col3,t3.col3) new_col3,
case when t1.col1 is not null then 'table1'
when t2.col1 is not null then 'table2'
when t3.col1 is not null then 'table3' end as new_col1_source,
case when t1.col2 is not null then 'table1'
when t2.col2 is not null then 'table2'
when t3.col2 is not null then 'table3' end as new_col2_source,
case when t1.col3 is not null then 'table1'
when t2.col3 is not null then 'table2'
when t3.col3 is not null then 'table3' end as new_col3_source
from
table1 t1
left join table2 t2 on t1.id = t2.id
left join table3 t3 on t1.id = t3.id
I'm not saying it's elegant. On the contrary, combining data and metadata in a single query inevitably results in clunkiness.