Case Expressions: SQL Server Select, Update and Crosstab Queries

CASE statements are very useful for categorizing data in SQL Server queries. Consider the following SQL statement:

USE pubs
GO
SELECT 'Price Category' =
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 10 THEN 'Very Reasonable Title'
WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
ELSE 'Expensive book!'
END,
CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
GO

This would yield a resultset like this:

Price Category	Title
Not yet priced	The Psychology of Co
Not yet priced	Net Etiquette
Very Reasonable Title	The Gourmet Microwav
Very Reasonable Title	You Can Combat Compu
Very Reasonable Title	Life Without Fear
Very Reasonable Title	Emotional Security:
Coffee Table Title	Is Anger the Enemy?
Coffee Table Title	Fifty Years in Bucki
Coffee Table Title	Cooking with Compute

One can easily see that this is much easier than writing a UNION query which would combine several individual queries selecting each price break using a WHERE clause.

SELECT Price as ‘Not Yet Priced', CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
WHERE isnull(Price)
ORDER BY price
UNION
SELECT Price as ‘Not Yet Priced', CAST(title AS varchar(20)) AS ‘Very Reasonable'
FROM titles
WHERE Price < 10
ORDER BY price
UNIOMay 23, 2006S varchar(20)) AS 'Coffee Table Title
FROM titles
Where Price >10
ORDER BY price
GO

In a similar vein, one could use the CASE statement to create columns in a crosstab query when one knows the expected results beforehand:

USE pubs
GO
SELECT title_id,
SUM(CASE WHEN month(ord_date)between 1 and 3 THEN qty ELSE 0 END) AS Q1,
SUM(CASE WHEN month(ord_date) between 4 and 6 THEN qty ELSE 0 END) AS Q2,
SUM(CASE WHEN month(ord_date)between 7 and 9 THEN qty ELSE 0 END) AS Q3,
SUM(CASE WHEN month(ord_date)between 10 and 12 THEN qty ELSE 0 END) AS Q4
FROM sales
group by title_id, qty

This query would yield the following result:

Title_ID	Q1	Q2	Q3	Q4
PS2091	0	0	3	0
BU1032	0	0	5	0
BU1032	0	0	10	0
MC2222	0	0	0	10
PS2091	0	0	10	0
BU7832	0	0	0	15
MC3021	0	0	15	0
PS3333	0	15	0	0
PS1372	0	20	0	0
PS2091	0	0	20	0
TC4203	0	20	0	0
TC7777	0	20	0	0
BU1111	25	0	0	0
MC3021	0	0	25	0
PS2106	0	25	0	0
PS7777	0	25	0	0
PC1035	0	30	0	0
BU2075	35	0	0	0
TC3218	0	40	0	0
PC8888	0	50	0	0
PS2091	0	0	75	0

CASE has two formats:

The simple CASE function compares an expression to a set of simple expressions to determine the result.

The searched CASE function evaluates a set of Boolean expressions to determine the result.

Both formats support an optional ELSE argument. ntax

Simple CASE function:

CASE input_expression
    WHEN when_expression THEN result_expression
        [ ... n ]
    [
        ELSE else_result_expression
    ]
END

The simple case statement allows only an equality check. If the input matches the when expression, the then clause is executed.

Searched CASE function:

CASE
    WHEN Boolean_expression THEN result_expression
        [ ... n ]
    [
        ELSE else_result_expression
    ]
END

The searched case is more powerful (but slightly slower). It allows for complicated Boolean expressions in each when clause.

CASE can be used anywhere an expression can be used: in the SELECT fields like, IN statement, WHERE clauses, and DELETE, APPEND, and UPDATE statements. The only caveat is that expressions used within the CASE statement must be of compatible data types.

Metro NY / NJ SQL Server Consultants

We specialize is custom database software. Call us for a free consultation (973) 635 0080 or email us at paladn.com

 

Database programming: Database Development
Computer Programming: Web Site Services
Computer Programming: Custom Software
Computer Consulting: IT Consulting
Paladin Consultants, Inc. Home Page
Computer Consulting: Contact Us

		Copyright © 2001 by Paladin Consultants,Inc. Chatham, NJ All Rights Reserved This page revised   May 23, 2006