New column with value equal to first column value that is not null-CodePudding

I have a SQL table that looks like this:

id	A	B	C
3	NULL	NULL	1
7	2	NULL	5
1	NULL	9	2

I want to add column D which looks at some columns A, B, and C and is equal to the value in the first one that is not null. I also want a column with the column I ended up choosing:

id	A	B	C	D	Choice
3	NULL	NULL	1	1	C
7	2	NULL	5	2	A
1	NULL	9	2	9	B

CodePudding user response：

You can COALESCE the column values e.g.

declare @demo table (id int, A int null, B int null, C int null);
insert into @demo (id, A,B,C) values
( 3,   NULL, NULL,  1),
( 7,   2,    NULL,  5),
( 1,   NULL, 9,     2);

select id, A,B,C, COALESCE(A,B,C,0) as firstNonNull
from @demo

Edit: as has been pointed out in the comments the OP wanted both the value and the name of the column in which that value was found. Here is my amended example

select ID, A, B, C, COALESCE(A, B, C) as D,
    case COALESCE(A, B, C)
        when A THEN 'A'
        when B THEN 'B'
        when C THEN 'C'
        else 'None'
    end as Choice
from @demo