SQL Server SQL case statement - Multiple conditions

cwxwcias 于 2023-06-28 发布在其他

关注(0)|答案(1)|浏览(106)

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

sql-server

来源：https://stackoverflow.com/questions/18128331/sql-case-statement-multiple-conditions

1条答案

按热度按时间

5fjcxozz1#

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.

赞(0）回复(0）举报 2023-06-28

我来回答

SQL Server SQL case statement - Multiple conditions

1条答案

相关问题

热门标签

最新问答