|
Execute the following
T-SQL example scripts in SQL Server Management Studio Query Editor to demonstrate the application of the CASE function for translating codes and formatting numbers.
-- CASE function usage in SELECT list for formatting & translation
SELECT ProductID,
ProductName = Name,
Price = CASE -- currency format
WHEN ListPrice > 0 THEN '$' + CONVERT(VARCHAR(16),ListPrice,1)
ELSE ''
END,
ProductCategory = CASE ProductLine
WHEN 'R' THEN 'Road Bikes'
WHEN 'M' THEN 'Mountain Bikes'
WHEN 'T' THEN 'Touring Bikes'
WHEN 'S' THEN 'Accessories'
ELSE 'Part only'
END
FROM AdventureWorks2008.Production.Product
ORDER BY ProductCategory,
ProductName;
/* ProductID ProductName Price ProductCategory
....
330 Touring End Caps Part only
513 Touring Rim Part only
847 Headlights - Dual-Beam $34.99 Road Bikes
848 Headlights - Weatherproof $44.99 Road Bikes
838 HL Road Frame - Black, 44 $1,431.50 Road Bikes
839 HL Road Frame - Black, 48 $1,431.50 Road Bikes .... */
------------
-- SQL Server case function - when...then...
-- Using case to translate codes - lookup table replacement
USE pubs;
SELECT [Book 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 'Waiting for category'
END,
Title = CAST(title AS VARCHAR(30)),
Price = price
FROM titles
WHERE price IS NOT NULL
ORDER BY [Book Category],
Price
GO
/* Results
Book Category Title Price
Business You Can Combat Computer Stress 2.99
Business Cooking with Computers: Surrep 11.95
Business The Busy Executive's Database 19.99
Business Straight Talk About Computers 19.99
Modern Cooking The Gourmet Microwave 2.99
Modern Cooking Silicon Valley Gastronomic Tre 19.99
Popular Computing Secrets of Silicon Valley 20.00
Popular Computing But Is It User Friendly? 22.95
Psychology Life Without Fear 7.00
Psychology Emotional Security: A New Algo 7.99
Psychology Is Anger the Enemy? 10.95
Psychology Prolonged Data Deprivation: Fo 19.99
Psychology Computer Phobic AND Non-Phobic 21.59
Traditional Cooking Fifty Years in Buckingham Pala 11.95
Traditional Cooking Sushi, Anyone? 14.99
Traditional Cooking Onions, Leeks, and Garlic: Coo 20.95
*/
------------
Related link:
Using the CASE expression instead of dynamic SQL in SQL Server
|
