How to use the CASE expression for report formatting? 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