Under conditions defined in a query, the query can replace data in a results set with other data. For example, the following query uses the CASE expression to replace certain price data with text comments:
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,
'Shortended Title' = CONVERT(varchar(20), title),
Category =
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
FROM titles
ORDER BY price
The results look like this:
Price Category Shortended Title Category --------------------- -------------------- ------------------- Not yet priced Net Etiquette Popular Computing Not yet priced The Psychology of Co Not yet categorized Very Reasonable Title The Gourmet Microwav Modern Cooking Very Reasonable Title You Can Combat Compu Business Very Reasonable Title Life Without Fear Psychology Very Reasonable Title Emotional Security: Psychology Coffee Table Title Is Anger the Enemy? Psychology Coffee Table Title Cooking with Compute Business Coffee Table Title Fifty Years in Bucki Traditional Cooking Coffee Table Title Sushi, Anyone? Traditional Cooking Coffee Table Title Prolonged Data Depri Psychology Coffee Table Title Silicon Valley Gastr Modern Cooking Coffee Table Title Straight Talk About Business Coffee Table Title The Busy Executive's Business Expensive book! Secrets of Silicon V Popular Computing Expensive book! Onions, Leeks, and G Traditional Cooking Expensive book! Computer Phobic AND Psychology Expensive book! But Is It User Frien Popular Computing (18 row(s) affected)