How to use Case Statement for Conditional Formatting in Select Query - SQL Server / TSQL Tutorial Part 116

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

How to use Case Statement in SQL Server - SQL Server / TSQL Tutorial


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


How to use Case Statement for Conditional Formatting in SQL Query - SQL Server / TSQL Tutorial


1 comment: