Case Statement

The SQL Server case statement is a conditional statement that returns a single value based on the evaluation of a statement. Case statements can be used in a SELECT list, WHERE clause and even an ORDER BY clause. Case statement can also be nested. This provides a lot of pliability for evaluating multiple expressions.

We’ll start by walking through a simple case statement. There are two ways to formulate a case statement. One is to present the column or variable to evaluate after the word case. It should be noted that the else portion of a case statement is completely optional.

Basic Equality Evaluation

[cc lang=”sql”]
DECLARE @Season varchar(100)
SET @Season = ‘Summer’
DECLARE @Climate varchar(50)

SET @Climate =
CASE @Season
WHEN ‘Winter’ THEN ‘Cold’
WHEN ‘Spring’ THEN ‘Mild’
WHEN ‘Summer’ THEN ‘Hot’
WHEN ‘Fall’ THEN ‘Cool’
ELSE ‘Unknown’
END

SELECT @Climate
[/cc]
That syntax works well when evaluating whether single variable is equal to a value. If we want to add additional criteria then we have to formulate the statement a different way.

Basic Multi-Evaluation

[cc lang=”sql”]
DECLARE @Temperature int
SET @Temperature = 64
DECLARE @Climate varchar(50)

SET @Climate =
CASE
WHEN @Temperature < 0 THEN 'Way Too Cold' WHEN @Temperature BETWEEN 0 AND 50 THEN 'Cold' WHEN @Temperature BETWEEN 51 AND 60 THEN 'Cool' WHEN @Temperature BETWEEN 61 AND 70 THEN 'Mild' WHEN @Temperature BETWEEN 71 AND 80 THEN 'Warm' WHEN @Temperature BETWEEN 81 AND 100 THEN 'Hot' WHEN @Temperature > 100 THEN ‘Way Too Hot’
ELSE ‘Unknown’
END

SELECT @Climate
[/cc]
Since our test does not consist of a simple “equals” we need to move each condition into the WHEN evaluator.

Evaluating from a Column

The examples above outlined the case statement returning a value to a variable, however we can also create a calculated column on the fly using CASE. To run this example, download the presidents table here.
[cc lang=”sql”]
SELECT
President
,WasElected =
(CASE
WHEN ElectoralVotes IS NULL THEN 0
ELSE 1
END)
FROM dbo.Presidents p
[/cc]
As you can see, when the ElectoralVotes column is null then we set our new virtual column equal to 0 or Not Elected.

Summarizing and Pivoting Data

The case statement is also useful in summarizing and pivoting data. The example below uses the case statement to count the president’s ages and SUM the age at inauguration into the appropriate category.
[cc lang=”sql”]
SELECT
AgeUnder45 =
SUM((CASE WHEN AgeAtInauguration < 45 THEN 1 END)) ,AgeBetween45And55 = SUM((CASE WHEN AgeAtInauguration BETWEEN 45 AND 55 THEN 1 END)) ,AgeBetween56And65 = SUM((CASE WHEN AgeAtInauguration BETWEEN 56 AND 65 THEN 1 END)) ,AgeBetween56And65 = SUM((CASE WHEN AgeAtInauguration BETWEEN 56 AND 65 THEN 1 END)) ,AgeOver65 = SUM((CASE WHEN AgeAtInauguration > 65 THEN 1 END))
FROM dbo.Presidents p
[/cc]