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.

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