Set value of columns to 0 based on priority?

Question

I have a table that looks like the below:

ID	Valid	Expired	NA	WIP
3	1	1	0	0
2	0	1	0	1
5	0	0	1	1

I have a requirement to build some code which based on the column name, sets only one column to 1, based on the priority of the columns. The priority is as follows:

• VALID
• EXPIRED
• NA
• WIP

So if anything has a Valid flag of 1, everything else should show as 0. If Valid is 0, then it should move down the priority and check if the Expired column is 1; if it is then everything else should show as 0, and so on... like below:

ID	Valid	Expired	NA	WIP
3	1	0	0	0
2	0	1	0	0
5	0	0	1	0

I have actually achieved this already, but it's a bit of a hefty case statement (case when valid = 1 then 0, case when valid = 0 and expired = 1...)

Does anyone else have any better approaches to achieve this?

Answer 1

I would also use case expression:

select
    id,
    valid,
    case when valid                > 0 then 0 else expired end expired,
    case when valid + expired      > 0 then 0 else na      end na,
    case when valid + expired + na > 0 then 0 else wip     end wip
from t