Best way to do nested case statement logic in SQL Server

Question

I m writing an SQL Query  where a few of the columns returned need to be calculated depending on quite a lot of conditions   I m currently using nested case statements  but its getting messy  Is there a better  more organised and or readable  way    I am using Microsoft SQL Server  2005     A simplified example   SELECT     col1      col2      col3      CASE         WHEN condition          THEN             CASE                 WHEN condition1                  THEN                     CASE                          WHEN condition2                         THEN calculation1                         ELSE calculation2                     END                 ELSE                     CASE                          WHEN condition2                         THEN calculation3                         ELSE calculation4                     END             END         ELSE              CASE                  WHEN condition1                  THEN                      CASE                         WHEN condition2                          THEN calculation5                         ELSE calculation6                     END                 ELSE                     CASE                         WHEN condition2                          THEN calculation7                         ELSE calculation8                     END             END                 END AS  calculatedcol1       col4      col5 -- etc FROM table

User · Answer

I went through this and found all the answers super cool, however wants to add to answer given by @deejers

    SELECT
    col1,
    col2,
    col3,
    CASE
        WHEN condition1 THEN calculation1 
        WHEN condition2 THEN calculation2
        WHEN condition3 THEN calculation3
        WHEN condition4 THEN calculation4
        WHEN condition5 THEN calculation5         
    END AS 'calculatedcol1',
    col4,
    col5 -- etc
FROM table

you can make ELSE optional as its not mandatory, it is very helpful in many scenarios.

User · Answer

I personally do it this way  keeping the embedded CASE expressions confined   I d also put comments in to explain what is going on   If it is too complex  break it out into function   SELECT     col1      col2      col3      CASE WHEN condition THEN       CASE WHEN condition1 THEN         CASE WHEN condition2 THEN calculation1         ELSE calculation2 END       ELSE         CASE WHEN condition2 THEN calculation3         ELSE calculation4 END       END     ELSE CASE WHEN condition1 THEN        CASE WHEN condition2 THEN calculation5       ELSE calculation6 END     ELSE CASE WHEN condition2 THEN calculation7          ELSE calculation8 END     END AS  calculatedcol1       col4      col5 -- etc FROM table

User · Answer

a user-defined function may server better  at least to hide the logic - esp  if you need to do this in more than one query

User · Answer

You could try some sort of COALESCE trick  eg    SELECT COALESCE    CASE WHEN condition1 THEN calculation1 ELSE NULL END    CASE WHEN condition2 THEN calculation2 ELSE NULL END    etc

User · Answer

a user-defined function may server better  at least to hide the logic - esp  if you need to do this in more than one query

User · Answer

We can combine multiple conditions together to reduce the performance overhead   Let there are three variables a b c on which we want to perform cases  We can do this as below   CASE WHEN a   1 AND b   1 AND c   1 THEN  1       WHEN a   0 AND b   0 AND c   1 THEN  0  ELSE  0  END

User · Answer

Here s a simple solution to the nested  Complex  case statment  --Nested Case Complex Expression  select  datediff dd Invdate  2009 01 31   1 as DaysOld       case when datediff dd Invdate  2009 01 31   1  gt 150 then 6 else         case when datediff dd Invdate  2009 01 31   1  gt 120 then 5 else              case when datediff dd Invdate  2009 01 31   1  gt 90 then 4 else                  case when datediff dd Invdate  2009 01 31   1  gt 60 then 3 else                      case when datediff dd Invdate  2009 01 31   1  gt 30 then 2 else                          case when datediff dd Invdate  2009 01 31   1  gt 30 then 1 end                      end                 end             end         end     end as Bucket from rm20090131atb   Just make sure you have an end statement for every case statement

User · Answer

We can combine multiple conditions together to reduce the performance overhead   Let there are three variables a b c on which we want to perform cases  We can do this as below   CASE WHEN a   1 AND b   1 AND c   1 THEN  1       WHEN a   0 AND b   0 AND c   1 THEN  0  ELSE  0  END

User · Answer

I personally do it this way  keeping the embedded CASE expressions confined   I d also put comments in to explain what is going on   If it is too complex  break it out into function   SELECT     col1      col2      col3      CASE WHEN condition THEN       CASE WHEN condition1 THEN         CASE WHEN condition2 THEN calculation1         ELSE calculation2 END       ELSE         CASE WHEN condition2 THEN calculation3         ELSE calculation4 END       END     ELSE CASE WHEN condition1 THEN        CASE WHEN condition2 THEN calculation5       ELSE calculation6 END     ELSE CASE WHEN condition2 THEN calculation7          ELSE calculation8 END     END AS  calculatedcol1       col4      col5 -- etc FROM table

User · Answer

You can combine multiple conditions to avoid the situation   CASE WHEN condition1   true AND condition2   true THEN calculation1       WHEN condition1   true AND condition2   false       ELSE  what so ever  END

User · Answer

You could try some sort of COALESCE trick  eg    SELECT COALESCE    CASE WHEN condition1 THEN calculation1 ELSE NULL END    CASE WHEN condition2 THEN calculation2 ELSE NULL END    etc

User · Answer

You could try some sort of COALESCE trick  eg    SELECT COALESCE    CASE WHEN condition1 THEN calculation1 ELSE NULL END    CASE WHEN condition2 THEN calculation2 ELSE NULL END    etc

User · Answer

I went through this and found all the answers super cool, however wants to add to answer given by @deejers

    SELECT
    col1,
    col2,
    col3,
    CASE
        WHEN condition1 THEN calculation1 
        WHEN condition2 THEN calculation2
        WHEN condition3 THEN calculation3
        WHEN condition4 THEN calculation4
        WHEN condition5 THEN calculation5         
    END AS 'calculatedcol1',
    col4,
    col5 -- etc
FROM table

you can make ELSE optional as its not mandatory, it is very helpful in many scenarios.

User · Answer

a user-defined function may server better  at least to hide the logic - esp  if you need to do this in more than one query

User · Answer

You could try some sort of COALESCE trick  eg    SELECT COALESCE    CASE WHEN condition1 THEN calculation1 ELSE NULL END    CASE WHEN condition2 THEN calculation2 ELSE NULL END    etc

User · Answer

This example might help you  the picture shows how SQL case statement will look like when there are if and more than one inner if loops

User · Answer

This example might help you  the picture shows how SQL case statement will look like when there are if and more than one inner if loops

User · Answer

I personally do it this way  keeping the embedded CASE expressions confined   I d also put comments in to explain what is going on   If it is too complex  break it out into function   SELECT     col1      col2      col3      CASE WHEN condition THEN       CASE WHEN condition1 THEN         CASE WHEN condition2 THEN calculation1         ELSE calculation2 END       ELSE         CASE WHEN condition2 THEN calculation3         ELSE calculation4 END       END     ELSE CASE WHEN condition1 THEN        CASE WHEN condition2 THEN calculation5       ELSE calculation6 END     ELSE CASE WHEN condition2 THEN calculation7          ELSE calculation8 END     END AS  calculatedcol1       col4      col5 -- etc FROM table

User · Answer

Wrap all those cases into one     SELECT     col1      col2      col3      CASE         WHEN condition1 THEN calculation1          WHEN condition2 THEN calculation2         WHEN condition3 THEN calculation3         WHEN condition4 THEN calculation4         WHEN condition5 THEN calculation5         ELSE NULL              END AS  calculatedcol1       col4      col5 -- etc FROM table

User · Answer

Wrap all those cases into one     SELECT     col1      col2      col3      CASE         WHEN condition1 THEN calculation1          WHEN condition2 THEN calculation2         WHEN condition3 THEN calculation3         WHEN condition4 THEN calculation4         WHEN condition5 THEN calculation5         ELSE NULL              END AS  calculatedcol1       col4      col5 -- etc FROM table

User · Answer

I personally do it this way  keeping the embedded CASE expressions confined   I d also put comments in to explain what is going on   If it is too complex  break it out into function   SELECT     col1      col2      col3      CASE WHEN condition THEN       CASE WHEN condition1 THEN         CASE WHEN condition2 THEN calculation1         ELSE calculation2 END       ELSE         CASE WHEN condition2 THEN calculation3         ELSE calculation4 END       END     ELSE CASE WHEN condition1 THEN        CASE WHEN condition2 THEN calculation5       ELSE calculation6 END     ELSE CASE WHEN condition2 THEN calculation7          ELSE calculation8 END     END AS  calculatedcol1       col4      col5 -- etc FROM table

User · Answer

Here s a simple solution to the nested  Complex  case statment  --Nested Case Complex Expression  select  datediff dd Invdate  2009 01 31   1 as DaysOld       case when datediff dd Invdate  2009 01 31   1  gt 150 then 6 else         case when datediff dd Invdate  2009 01 31   1  gt 120 then 5 else              case when datediff dd Invdate  2009 01 31   1  gt 90 then 4 else                  case when datediff dd Invdate  2009 01 31   1  gt 60 then 3 else                      case when datediff dd Invdate  2009 01 31   1  gt 30 then 2 else                          case when datediff dd Invdate  2009 01 31   1  gt 30 then 1 end                      end                 end             end         end     end as Bucket from rm20090131atb   Just make sure you have an end statement for every case statement

User · Answer

a user-defined function may server better  at least to hide the logic - esp  if you need to do this in more than one query

User · Answer

You can combine multiple conditions to avoid the situation   CASE WHEN condition1   true AND condition2   true THEN calculation1       WHEN condition1   true AND condition2   false       ELSE  what so ever  END

[sql] Best way to do nested case statement logic in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to select

Examples related to nested