Return ONLY non-null values
-
Friends,
I have a scenario where there will be only one record that is populated with non-null value in 4 columns. I need to pick only the non-null value and return as single record. Please help.
Ex:
Co11 Col2 Col3 Col4
----- ------ ------- -----
abc null null null
null xyz null null
null null pqr null
null null null mno
the result should look like
abc xyz pqr mno
thanks in advance
M.
-
The coalesce function will do exactly that.
select coalesce(col1, col2, col3, co4)
from myTable
Although the result will be
abc
xyz
pqr
mno
in 4 records rather than 1 record with 4 fields.
Would that do?
If not, then you can do
select
(select top 1 col1 from myTable where col1 is not null),
(select top 1 col2 from myTable where col2 is not null),
(select top 1 col3 from myTable where col3 is not null),
(select top 1 col4 from myTable where col4 is not null)
Which will give you exactly what you want. The "top 1" is there to ensure an error does not occur if you have more than one non-null value in one of the columns.
Hope that helps!
Cheers
-
thanks dude. the second solution works for me.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply