Using CASE

The CASE expression is used to evaluate several conditions and return a single value for each condition. For example, it allows an alternative value to be displayed depending on the value of a column. This change in data is temporary; therefore, there are no permanent changes to the data.

The CASE expression consists of:

• The CASE keyword.

• The column name to transform.

• WHEN clauses specifying the expressions to search for and THEN clauses specifying the expressions to replace them with.

• An optional ELSE clause defining the expression returned if no comparison operation evaluates to TRUE.

• The END keyword.

• An optional AS clause defining an alias for the CASE expression.

A common use of the CASE expression is to replace codes or abbreviations with more readable values. The following example uses the CASE function to change the display of product line categories to make them more understandable.

USE AdventureWorks2008R2;
GO
SELECT   ProductNumber, Category =
      CASE ProductLine
         WHEN 'R' THEN 'Road'
         WHEN 'M' THEN 'Mountain'
         WHEN 'T' THEN 'Touring'
         WHEN 'S' THEN 'Other sale items'
         ELSE 'Not for sale'
      END,
   Name
FROM Production.Product
ORDER BY ProductNumber;
GO

Another use of CASE is to categorize data. The following query uses the CASE function to categorize prices.

USE AdventureWorks2008R2;
GO
SELECT   ProductNumber, Name, "Price Range" = 
      CASE 
         WHEN ListPrice =  0 THEN 'Mfg item - not for resale'
         WHEN ListPrice < 50 THEN 'Under $50'
         WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
         WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
         ELSE 'Over $1000'
      END
FROM Production.Product
ORDER BY ProductNumber ;
GO

For more examples, see CASE (Transact-SQL).