case statement in sql

case in sql

Case statement in sql helps to display customize data in select statement. Sometimes we need to display data in different manner, in this situation we can use case statement with select query.

Example of Case

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.

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