SQL Server: Tips and Tricks - 2 (2017)

How to use Case Statement for Conditional Formatting in Select Query

Scenario:

You are working as SQL Server developer, you need to query dbo.Customer table that has CountryShortName column with country abbreviations. You want to generate another columns with Country Full name by using CountryShortName column values. How would you do that?

Solution:

You can use Case expressions to generate this new column depending upon the value of CountryShortName. In our example we are using only single columns but you can use multiple columns and check for multiple conditions.

Let's create dbo.Customer table with some sample data and then we will write our Select statement with Case expression.

Create table dbo.Customer

(Id int,

FName VARCHAR(50),

LName VARCHAR(50),

CountryShortName CHAR(2))

GO

insert into dbo.Customer

Values (

1,'Raza','M','PK'),

(2,'Rita','John','US'),

(3,'Sukhi','Singh',Null)

1) You can use Column Name for which you want to check the values right after Case as shown below. Then write all the conditions on that column and finally use End as NewColumnName

Select

FName,

LName,

CountryShortName,

Case CountryShortName

When 'Pk' Then 'Pakistan'

When 'US' Then 'United States of America'

When 'IN' Then 'India'

Else 'Not Provided'

End AS CountryFullName

From dbo.Customer

If you don't like to use Else part, you can remove that but in case when you will have value and it does not match with your conditions, It will return Null. In my case, If value does not match with my conditions, I would like to show as "Not Provided" by using Else part.

2) Don't use Column Name right after Case keyword

You can also write the case statement as shown below. In below example, we did not write Column Name right after Case. In this case we have to type column after every When. This way of writing is used when you want to check conditions for multiple columns or range of values.

Select

FName,

LName,

CountryShortName,

Case

When CountryShortName='Pk' Then 'Pakistan'

When CountryShortName='US' Then 'United States of America'

When CountryShortName='IN' Then 'India'

Else 'Not Provided'

End AS CountryFullName

From dbo.Customer