SELECT CASE – shannonlowder.com

A useful skill to learn in SQL is how to translate a value into a description. You’ll use it all the time. Whether you’re turning M|F into Male or Female, or turning T|F into True or False, you’ll have to be able to make the translation as quickly as possible. The easiest way to do that is to master the CASE expression. Let’s dive right in.

(Please note the following examples have been updated to work with AdventureWorks on 2008R2)

For our first example, we’re going to look at the Employee table, and translate the Salaried Flag from 0, meaning Hourly and 1, meaning Salaried into the actual descriptions Hourly or Salaried.

SELECT DISTINCT 
      SalariedFlag
    , CASE WHEN SalariedFlag = 1 THEN 'Salaried' ELSE 'Hourly' END as SalaryDescription
FROM HumanResources.Employee

When you run this query, you get the following results.

The CASE statement lets you do a logical test, and if it’s true do one thing, and if it results in a false, does another. There are actually two ways to construct this CASE statement. The way I’ve already shown is great if you need to test two different values. Let’s say you needed to test the SalariedFlag and Gender, the syntax above is better for that. But if you need to handle multiple cases for the same column, then you’ll want to use the following syntax.

SELECT DISTINCT 
      SalariedFlag
    , CASE SalariedFlag 
         WHEN 1 THEN 'Salaried' 
         ELSE 'Hourly' 
      END as SalaryDescription
FROM HumanResources.Employee

Notice the difference in how it’s formed, in the second method we use CASE <column> WHEN, and before we used CASE WHEN <test scenario>. Both of these methods are useful, you just need to learn when to use each. The second query still returns the same results.

Let’s take a look at when you’d want to use this second method. In AdventureWorks for 2008R2, there is a constraint for the Gender column in HumanResources.Employee, it requires you to enter M or F. I’ve altered the Constraint so you can now enter M, F, or U, for those times you really don’t know and you can’t ask.

We want a CASE statement that can handle all three scenarios.

SELECT DISTINCT 
    Gender
    , CASE Gender
        WHEN 'M' THEN 'Male'
        WHEN 'F' THEN 'Female'
        ELSE 'Unknown'
      END as GenderDescription
FROM HumanResources.Employee

When you run this query against the my test server, you’ll see the following results.

My database has an entry for Pat. Now that we’ve seen a scenario where we’d want to use the CASE <column> WHEN version… let’s look at the other scenario. With the other scenario we can test multiple columns with multiple scenarios.

In this example we’ll build a custom description based on color and list price. When the color is black, we’ll refer to the color as “Gloss Black”, otherwise we’ll only refer to the value stored in the color column. When the price is greater than or equal to $1000, we’ll refer to the product as expensive, when it’s under $1000, we’ll refer to it as a “Bargain”.

SELECT TOP 10
      Name
    , Color
    , listPrice
    , CASE    
        WHEN Color = 'Black' AND ListPrice >= 1000 THEN 'Expensive Gloss Black'
        WHEN Color = 'Black' AND ListPrice < 1000 THEN 'Bargain Gloss Black'
        WHEN ListPrice >= 1000 THEN 'Expensive ' + Color
        WHEN ListPrice < 1000 THEN 'Bargain ' + Color
      END as CustomDescription    
FROM production.Product
WHERE 
    ListPrice > 0
    AND Color IS NOT NULL

Notice how we handle muliple columns in this scenario. The results of this query appear below.

Learning to use the CASE expression will really take you to the next level in your queries. Being able to make changes to the results based on values in your data set is a fundamental concept in report writing. Let me know if you have any questions!

Leave a Reply Cancel reply