The CASE function is used to evaluate several conditions and return a single value for each condition. A common use of the CASE function is to replace codes or abbreviations with more readable values. The following query uses the CASE function to rename book categories so that they are more understandable.
USE pubs
SELECT
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END AS Category,
CONVERT(varchar(30), title) AS "Shortened Title",
price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY 1
Here is the result set:
category shortened title Price
------------------- ------------------------------ -------
Business Cooking with Computers: Surrep 11.95
Business Straight Talk About Computers 19.99
Business The Busy Executive's Database 19.99
Business You Can Combat Computer Stress 2.99
Modern Cooking Silicon Valley Gastronomic Tre 19.99
Modern Cooking The Gourmet Microwave 2.99
Popular Computing But Is It User Friendly? 22.95
Popular Computing Secrets of Silicon Valley 20.00
Psychology Computer Phobic AND Non-Phobic 21.59
Psychology Emotional Security: A New Algo 7.99
Psychology Is Anger the Enemy? 10.95
Psychology Life Without Fear 7.00
Psychology Prolonged Data Deprivation: Fo 19.99
Traditional Cooking Fifty Years in Buckingham Pala 11.95
Traditional Cooking Onions, Leeks, and Garlic: Coo 20.95
Traditional Cooking Sushi, Anyone? 14.99
(16 row(s) affected)
Another use of CASE is to categorize data. The following query uses the CASE function to categorize prices.
SELECT
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 AS "Price Category",
CONVERT(varchar(20), title) AS "Shortened Title"
FROM pubs.dbo.titles
ORDER BY price
Here is the result set:
Price Category Shortened Title
--------------------- --------------------
Not yet priced The Psychology of Co
Not yet priced Net Etiquette
Very Reasonable Title You Can Combat Compu
Very Reasonable Title The Gourmet Microwav
Very Reasonable Title Life Without Fear
Very Reasonable Title Emotional Security:
Coffee Table Title Is Anger the Enemy?
Coffee Table Title Cooking with Compute
Coffee Table Title Fifty Years in Bucki
Coffee Table Title Sushi, Anyone?
Coffee Table Title The Busy Executive's
Coffee Table Title Straight Talk About
Coffee Table Title Silicon Valley Gastr
Coffee Table Title Prolonged Data Depri
Expensive book! Secrets of Silicon V
Expensive book! Onions, Leeks, and G
Expensive book! Computer Phobic AND
Expensive book! But Is It User Frien
(18 row(s) affected)