Best way to do nested case statement logic in SQL Server

Question

I'm currently 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 it is getting messy. Is there a better (more organized 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

Answer 1

Try using COALESCE trick, like this:

SELECT COALESCE(

  CASE WHEN condition1 THEN calculation1 ELSE NULL END,

  CASE WHEN condition2 THEN calculation2 ELSE NULL END,

  etc...

)

If you want to learn SQL commands visit these SQL Commands Cheat Sheet.

To master SQL statements, queries and become proficient in SQL queries, enroll in an industry-recognized SQL Online Course.