CODEWITHSUNDEEP
sql-servert-sql
kazzi
kazzi

Reputation: 534

Case Statement if all NULL or at least one value present

I could be doing this wrong, but here's as much detail on where I'm at with my thinking.

I have a table, which we'll call table:

Test     ID     Value
test_1   01     50
test_2   01     NULL
test_3   01     40/50
test_1   02     NULL
test_2   02     NULL

Summary

I am just looking to make a CASE expression where I can if all the test values are NULL then 'No' if at least one test value has a value then 'Yes'.

Desired Output:

ID   Case
01   Yes
02   No

Current Query:

SELECT
    ID
   ,CASE WHEN t.test IN ('test_1','test_2','test_3') IS NOT NULL
        THEN 'Yes'
        ELSE 'No'  END
FROM table t

Error

Incorrect syntax near the keyword 'IS'.

Upvotes: 1

Views: 1174

Answers (1)

S3S
S3S

Reputation: 25112

Using an aggregate....

declare @table table (test varchar(6), id int, value varchar(24))
insert into @table
values
('test_1','01','50'),
('test_2','01',NULL),
('test_3','01','40/50'),
('test_1','02',NULL),
('test_2','02',NULL)

select
    t.id
    ,case when min(t.value) is null and max(t.value) is null then 'NO' else 'YES' end as [Case]
from
    @table t
group by
    t.id

And really, you only need to check it once.

select
    t.id
    ,case when max(t.value) is null then 'NO' else 'YES' end as [Case]
from
    @table t
group by
    t.id

Upvotes: 2

Related Questions