none
SQL Server Case Statement RRS feed

  • Question

  • Good afternoon,

    I am currently working on a data flow and have been given a specific requirement that i am trying to complete.

    In my table I have a column which is partially NULL due to a couple of reasons.

    What i'm trying to do is write a case statement within my select statement that has two conditions:

    When NULL use a different value from another column (which is pulled from another table using a join)

    If the column is still NULL (in both cases) then use a different value from another column in the table which will ensure the column is populated.

    So basically, if it's NULL do this, if its still NULL, then do this which will mean my column is populated as i intend.

    I've been playing around but have been unable to produce the required result. Is this something that can be achieved using a CASE statement? 

    Any help/advice would be appreciated.

    Thanks.

    Wednesday, January 22, 2020 3:59 PM

All replies

  • Hi Matt,

    A single COALESCE() function call will do what you need. It will pick up a very first NOT NULL value in a list.

    Along the following:

    COALESCE(column1, column2, column3)

    Wednesday, January 22, 2020 4:09 PM
  • Hi 

    I tried to understand your requirement. Please check following script . 

    IF OBJECT_ID('test1') IS NOT NULL drop table  test1 
    IF OBJECT_ID('test2') IS NOT NULL drop table  test2 
    go 
    create table test1(
    id int,
    A1 varchar(10))
    insert into test1 values
    (1,null),(2,'a'),(3,'b'),(4,null)
    create table test2(
    id int,
    A2 varchar(10))
    insert into test2 values
    (1,null),(2,'aa'),(3,null),(4,'bb')
    
    select *, 
    case when a.A1 is null and b.A2 is null then null 
    when a.A1 is null and b.A2 is not null then b.A2
    when a.A1 is not null then a.A1 end result
    from test1 a 
    join test2 b on a.id=b.id
    /*
    id          A1         id          A2         result
    ----------- ---------- ----------- ---------- ----------
    1           NULL       1           NULL       NULL
    2           a          2           aa         a
    3           b          3           NULL       b
    4           NULL       4           bb         bb
    */

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 23, 2020 6:14 AM