Use of Case in SQL Server

Written By:- Isha Malhotra
Our Website:-Tech Altum

Use of Case in SQL Server

Sometimes we need to show the data of table in different way at runtime. In this situation we can use case statement with select query.

For Example:-

Suppose we have the following table:-

Figure 1

In this table in gender column the data is ‘f’ for female and ‘m’ for male. If we run normal select query it will show data in same format but I want that it will show data in the manner that if it contains ‘f’ then it will display female and if it contain ‘m’ then it will display male.

So we use the following query using case:-

select id, name, case(gender) when 'f' then 'Female' when 'm' then 'Male' end as Gender from Enqdata;

the output of this query would be following:-

Figure 2

If you remember then in general we can use default value in cases which shows default value if no case match. Here we use else if we want to implement default.

For example see the Figure 1 and check the enq_type column. Suppose we want to show that if it 0 then it shows Enquiry open, if 1 then it shows Enquiry pending, if 2 then it shows Enquiry Closed. Otherwise it shows Enquiry not opened. In this situation we will write query in the following manner:-

select id, name, case(enq_type) when 0 then 'Enquiry Opened' when 1 then 'Enquiry Pending'

when 2 then 'Enquiry Closed' else 'Enquiry not opened' end as 'Enquiry Status' from Enqdata;

the output of the this query is as follows:-

Figure 3

Hope you enjoyed the article.

hope you enjoyed this article. For any query you can mail me at malhotra.isha3388@gmail.com.

Join our Facebook page to get updates of Jobs, Interview Question, Course Details and Study Material

https://www.facebook.com/thetechaltum