Fill in column if NULL with data from next column that is NOT NULL

Question

Say I have the following table with a unique ID and 4 attribute columns...

|ID|  |Attribute 1|  |Attribute 2|  |Attribute 3|  |Attribute 4|
1          RED            NULL           BLUE          GREEN
2          NULL           BLUE           GREEN         NULL
3          GREEN          YELLOW         NULL          BLUE
4          YELLOW         NULL           NULL          GREEN

What can I use in SQL Server 2014 to say: If attribute 1 is NULL, use attribute 2, but if attribute 2 is NULL use Attribute 3, and so on... I was going to use a CASE statement, but I'm not sure how to go about it beyond CASE WHEN Attribute 1 IS NULL THEN Attribute 2, but then what if attribute 2 is NULL? How would I then select the next column value that is not NULL?

I want to somehow get the above, to this...

|ID|  |Attribute 1|  |Attribute 2|  |Attribute 3|  |Attribute 4|
1          RED            BLUE           GREEN        NULL
2          BLUE           GREEN          NULL         NULL
3          GREEN          YELLOW         BLUE         NULL
4          YELLOW         GREEN          NULL         NULL

Consider re-design you table: (id, attrno, color).

jarlh
– jarlh

2017-01-05 13:54:14 +00:00
Commented Jan 5, 2017 at 13:54 — jarlh
– jarlh, Commented Jan 5, 2017 at 13:54

Gordon Linoff · Accepted Answer · 2017-01-07 03:09:17Z

2

This is a pain, but in SQL Server, you can use outer apply and some additional logic:

select t.id,
       v.attribute1, v.attribute2, v.attribute3, v.attribute4
from t outer apply
     (select max(case when seqnum = 1 then a end) as attribute1,
             max(case when seqnum = 2 then a end) as attribute2,
             max(case when seqnum = 3 then a end) as attribute3,
             max(case when seqnum = 4 then a end) as attribute4             
      from (select v.*, row_number() over (order by n) as seqnum
            from (values (1, t.attribute1), (2, attribute2), (3, t.attribute3), (4, t.attribute4)
                 ) v(n, a)
            where a is not null
           ) v
      ) v;

This unpivots the data and then repivots the values.

edited Jan 7, 2017 at 3:09

answered Jan 5, 2017 at 13:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

OhioMike1987 Over a year ago

For some reason the CASE statements are not accepting the column value a. I am getting the "Invalid column name 'a'" error.

Gordon Linoff Over a year ago

@OhioMike1987 . . . Ooops, there should have been an a.* in that subselect.

Lajos Arpad · Accepted Answer · 2017-01-05 14:56:55Z

0

isNull gets two parameters: an expression and a fallback. You need to use a combination of isNull calls:

isNull(attribute1, isNull(attribute2, isNull(attribute3, attribute4)))

Documentation.

answered Jan 5, 2017 at 14:56

Lajos Arpad

80.4k42 gold badges123 silver badges235 bronze badges

Collectives™ on Stack Overflow

Fill in column if NULL with data from next column that is NOT NULL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related