CODEWITHSUNDEEP
mysqlsqlsql-servercase
user1570210
user1570210

Reputation: 1219

SQL case statement - Multiple conditions

I am writing a case statement where I need to check columns and assign a value . But what if a row qualifies for multiple cases ? For example in following table I want assign a bucket when COLA and/or COLB is null ; so in first case both are NULL , so how will I tell SQL case statement to assign "Both are NULL " when COLA and COLB are null. I can easily assign when COLA or COLB is null . At my work I am checking 8 columns , so I need to find every combination and right case for that ? There has to be an easy way .

**RowNumber     COLA              COLB                Case**
1               Null              Null                **Both are NULL** 
2               Null              B                   A is null
3               A                 Null                B is null 
4               AA                BB                  NULL
5               CC                Null                B is null

Upvotes: 1

Views: 16372

Answers (1)

James Harcourt
James Harcourt

Reputation: 6379

Given you have 8 columns, you probably need to do something like this:

WITH t AS (
    SELECT
        CASE WHEN (colA IS NULL AND colB IS NULL AND colC IS NULL AND colD IS NULL AND 
                   colE IS NULL AND colF IS NULL AND colG IS NULL AND colH IS NULL) THEN 'ALL' ELSE '' END [ALL],
        CASE WHEN colA IS NULL THEN 'A' ELSE '' END [A],
        CASE WHEN colB IS NULL THEN 'B' ELSE '' END [B],
        CASE WHEN colC IS NULL THEN 'C' ELSE '' END [C],
        CASE WHEN colD IS NULL THEN 'D' ELSE '' END [D],
        CASE WHEN colE IS NULL THEN 'E' ELSE '' END [E],
        CASE WHEN colF IS NULL THEN 'F' ELSE '' END [F],
        CASE WHEN colG IS NULL THEN 'G' ELSE '' END [G],
        CASE WHEN colH IS NULL THEN 'H' ELSE '' END [H] 
        FROM
            <TABLENAME>)                                

SELECT 
    CASE WHEN [ALL] = 'ALL' THEN 'ALL are NULL'
    ELSE [ALL] + ',' + A + ',' + B + ',' + C + ',' + D + ',' 
               + E + ',' + F + ',' + G + ',' + H + ' are NULL' 
END
FROM T

In the final select statement, you could make further alterations to present the results as you want.

Upvotes: 7

Related Questions