How to know which column is giving the value in Coalesce

Question

Hi

I Have Coalesce(Col1,col2,col3) as result

i want know which column is returning value in the output....

Thank u in advance

Answer 1

It will return the first NOT NULL column value as output. If you want to see what is coming exactly, you may add Col1, Col2 and Col3 also as part of SELECT column list along with COALESCE function.

The function is written in the order of column priority that your requirement may have.

Thanks!

Answer 2

You can also have one more column like this

select Coalesce(Col1,col2,col3), case when Coalesce(Col1,col2,col3)=col1 then 'Col1'

when Coalesce(Col1,col2,col3)=col2 then 'Col2'

when Coalesce(Col1,col2,col3)=col3 then 'Col3'

end+' is shown' extra_column

from tab1;

go

---

Many Thanks & Best Regards, Hua Min