DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
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

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE