Paladin Consultants, LLC

"Good Information is just good business"

JA slide show
Home Sitemap
Print E-mail

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 This e-mail address is being protected from spambots. You need JavaScript enabled to view it

 

 

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
 
Bookmark and Share