How to get the status?

[Q. John Chen]

I have a table with columns c1, c2, c3, c4.

if all nulls or blanks. Status = 0
if c1 assigned but no c2, c3, and c4, then status = 1
if c2 assigned but no c3 and c4, then status = 2
if c3 .. then ..
if c4 .. then ..

I want to have one SQL to get the status like (ignored checking for
blanks here for demo)

SELECT Status = (if not c4 is null then 4
else not c3 is null then 3
else not c2 is null then 2
else not c1 is null then 1
else 0)
FROM mytable.

Thought of using CASE ... WHEN ... but it is only on one colum.

Any better idea.

Thanks

John

[Simon Hayes]

Re: How to get the status?

I'm not sure why you believe that CASE can only reference a single
column (admittedly, Books Online shows only single-column examples):

select case
when coalesce(c1,c2, c3,c4) is null then 0
when c1 is not null and c2 is null and c3 is null and c4 is null then
1
when c1 is null and c2 is not null and c3 is null and c4 is null then
2
/* Add other combinations here */
else null end as 'Status'
from
(select null as 'c1', null as 'c2', null as 'c3', null as 'c4'
union all
select 1, null, null, null
union all
select null, 2, null, null) dt

Simon