Best way to do nested case statement logic in SQL Server

0 votes

I'm creating a SQL query where some of the returned columns need to be calculated based on a lot of different factors. I'm using nested case statements right now, but it's confusing. Is there a better way—one that is more readable and/or organized?

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
Sep 18 in Database by Kithuzzz
• 20,660 points
258 views

1 answer to this question.

0 votes

Try some sort of COALESCE trick:

SELECT COALESCE(
  CASE WHEN condition1 THEN calculation1 ELSE NULL END,
  CASE WHEN condition2 THEN calculation2 ELSE NULL END,
  etc...
)
answered Sep 19 by narikkadan
• 37,660 points

Related Questions In Database

0 votes
0 answers

I want to use CASE statement to update some records in sql server 2005

UPDATE dbo.TestStudents SET LASTNAME = ( ...READ MORE

Sep 2 in Database by Kithuzzz
• 20,660 points
112 views
0 votes
0 answers

How to update two tables in one statement in SQL Server 2005?

In one operation, I want to update ...READ MORE

Aug 20 in Database by Kithuzzz
• 20,660 points
82 views
0 votes
0 answers

Nested select statement in SQL Server

Why does the following not function? SELECT name ...READ MORE

Aug 30 in Database by Kithuzzz
• 20,660 points
61 views
0 votes
0 answers

How to DROP multiple columns with a single ALTER TABLE statement in SQL Server?

In one ALTER TABLE statement, I would ...READ MORE

Sep 2 in Database by Kithuzzz
• 20,660 points
94 views
0 votes
1 answer

How do I UPDATE from a SELECT in SQL Server?

MERGE INTO YourTable T USING ...READ MORE

answered Feb 3 in Database by Vaani
• 7,020 points
167 views
0 votes
0 answers

How do I UPDATE from a SELECT in SQL Server?

INSERT INTO Table (col1, col2, col3) SELECT col1, ...READ MORE

Feb 4 in Database by Vaani
• 7,020 points
71 views
0 votes
1 answer

SQL Server CASE .. WHEN .. IN statement

Two forms of CASE statements are getting ...READ MORE

answered Feb 8 in Database by Vaani
• 7,020 points
129 views
0 votes
0 answers

Selecting COUNT(*) with DISTINCT

In SQL Server 2005, I have a ...READ MORE

Feb 14 in Database by Neha
• 8,940 points
127 views
0 votes
1 answer

How to execute function in SQL Server 2008

The function appears not to be being ...READ MORE

answered Sep 11 in Database by narikkadan
• 37,660 points
112 views
0 votes
1 answer

Add a column with a default value to an existing table in SQL Server

Syntax: ALTER TABLE {TABLENAME} ADD {COLUMNNAME} {TYPE} {NULL|NOT ...READ MORE

answered Sep 12 in Database by narikkadan
• 37,660 points
227 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP