Post reply

Return ONLY non-null values

  • June 7, 2007 at 7:53 am

    #711413

    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

  • June 7, 2007 at 8:05 am

    #711420

    thanks dude.  the second solution works for me.

Viewing 2 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply