Archive

Posts Tagged ‘CASE Statement’

SQL Case Statement

December 22nd, 2010 No comments

Case statement evaluates a list of conditions and returns one of multiple possible result expressions. CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.

Let’s take a look at the following example, but first the syntax:

Simple CASE expression:
CASE input_expression
     WHEN when_expression THEN result_expression [ ...n ]
     [ ELSE else_result_expression ]
END
Searched CASE expression:
CASE
     WHEN Boolean_expression THEN result_expression [ ...n ]
     [ ELSE else_result_expression ]
END
Arguments

input_expression
Is the expression evaluated when the simple CASE format is used. input_expression is any valid expression.

WHEN when_expression
Is a simple expression to which input_expression is compared when the simple CASE format is used. when_expression is any valid expression. The data types of input_expression and each when_expression must be the same or must be an implicit conversion.

THEN result_expression
Is the expression returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE. result expression is any valid expression.

ELSE else_result_expression
Is the expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. else_result_expression is any valid expression. The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion.

WHEN Boolean_expression
Is the Boolean expression evaluated when using the searched CASE format. Boolean_expression is any valid Boolean expression.

Now, there are couple of techniques to make 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.

DECLARE @Fruit varchar(100)
SET @Fruit = 'Tropical'
DECLARE @FruitType varchar(50)
 
SET @FruitType =
CASE @Fruit
    WHEN 'Banana' THEN 'Tropical'
    WHEN 'Strawberry' THEN 'Non-Tropical'
    WHEN 'Blueberry' THEN 'Exotic'
    ELSE 'Unknown'
END
 
SELECT @FruitType

Using CASE statement with BETWEEN:

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

The CASE statement is also useful in summarizing and pivoting data. As an example below uses the case statement to count the president’s ages and SUM the age at inauguration into the appropriate category.

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
Categories: SQL Tags: , ,