Data Management

SQL refresher: How to use the CASE function in a select statement to make your data clear

Have data in your database that's not user friendly? Present it in a more understandable format with a CASE function.

2016-08-0112-54-39.jpg

The fundamental makeup of structured query language (SQL) is to "select" the data you're looking for, "from" the data source you want,"where" specific parameters are met. But the results aren't always easy to understand. When writing your select statement, you can creatively customize your report with functions and additional select statements. Let's look at using a CASE function in our SQL select statement.

What is a CASE function?

A CASE function allows you to display specific outputs based on parameters being met, not just the actual data within the parameters. Think of the popular content distribution service If This Then That. The IFTT services uses the CASE function to aggregate content by saying "if" this parameter is met, "then" publish "this" article.

SEE: IFTTT: The smart person's guide

Let's look at a sample table in a database (Table A). We'll call it the "customers" table. It has all the customer information we're looking for, but we want to differentiate the VIP customers from the standard customers.

Table A: Customers

cust_idlast_namefirst_namestate_codecitystatus
3233VADERDARTHNYNEW YORK1
2411SOLOHANCODENVER2
7298FETTJANGOMIDETROIT2
6418LARSOWENTXPLANO1

Notice the data structure for this table has the customer status denoted as a digit. In this instance, 1 denotes VIP status and 2 denotes standard status. If you were to send the output of this table to an executive, you'd probably get asked, "What do the ones and twos mean?" Prevent that question from coming up by using a CASE function in your select. Here's how.

SEE: SQL refresher: How to gather data from multiple tables

The syntax

Select cust_id, last_name, first_name, city, CASE IF status = '1' THEN "VIP" ELSE "STANDARD" END

From customers;

Output:

cust_idlast_namefirst_namecity(expression)
3232VADERDARTHNEW YORKVIP
2411SOLOHANDENVERSTANDARD
7298FETTJANGODETROITSTANDARD
6418LARSOWENPLANOVIP

By adding the CASE function to the select statement, you can have the 1s displayed as VIP on the report and the 2s as STANDARD. When using CASE, you have to have the "if" clause mentioned, followed by a "then" clause. In short, you're typing "if this parameter is met, then output what I want to the report." I'll show it here with notation to further explain:

CASE /* starting the CASE function */

IF /* put your "if" here */

Status = '1' /* specify which field in the database table and the parameter */

THEN 'VIP' /* specify what to display when your "if" condition is met */

ELSE 'STANDARD' /* specify what to display if your condition isn't met */

END /* this terminates the CASE function */

The beauty of the CASE function is you can have your code roll with more than one IF/THEN statement. If my sample table had more than two values for the customer status, I could have easily specified what to display by continuing with consecutive IF/THEN statements until all conditions were met. As usual, close out the function with the END statement.

Example:

CASE IF status = '1' THEN 'VIP'

ELSE IF status = '2' THEN 'STANDARD'

ELSE IF status = '3' THEN 'FAMILY'

END

One final note. If you run this SQL with the CASE function, the report you generate will list each column you mentioned in your select statement, but the column designated for the CASE function will denote as "expression". To remedy this, just add a label after you terminate the CASE function.

Example:

SELECT last_name, state_code, CASE IF status = '1' THEN 'VIP' ELSE 'STANDARD' END as status

FROM customers;

Better reports

The CASE function offers an easy way to present data found in a database table in a more user-friendly format. Let's be honest: Today's enterprise data models have a gazillion tables with several dependencies and keys that act as headers to more detailed information. The individuals requesting the report will not want to review a report that's full of "tech talk." They'll want data that's valuable to them and easy to understand. Allow CASE to do this for you.

Also read...

Other tips?

What SQL tricks do you use to make your reports easier to understand? Share your thoughts and advice with fellow TechRepublic members.

About Ant Pruitt

Ant Pruitt is an IT Support Professional with a passion for showing the non-geek how great technology can be. He writes for a variety of tech publications and hosts his own podcast. Ant is also an avid photographer and weight lifter.

Editor's Picks

Free Newsletters, In your Inbox