Multipurpose CASE expression

CASE expression evaluates the conditions given in WHEN clause and return the result of matched condition. It is very handy and useful in certain scenarios. We can see how it can be effectively used in many cases.

1 Simple condition

declare @gender char(1)
select @gender='M'
select case when @gender='M' then 'Male' when @gender='F' then 'Female' end as gender

The result is


2 Conditional aggregation

Using CASE expression you can conditional do some aggregations like SUM, COUNT etc. This is particularly using if you want to PIVOT the data.

Consider the following data and suppose you want to find out male and females counts as separate columns, you can use as shown below

declare @employee table(emp_id int, emp_name varchar(100), gender char(1))
insert into @employee
select 1,'Sankar','M' union all
select 2,'John','M' union all
select 3,'sumithra','F' union all
select 4,'Nilesh','M'

(case when gender='M' then 1 else 0 end) as male_count,
sum(case when gender='F' then 1 else 0 end) as female_count

The result is

male_count female_count
----------- ------------
3           1

3 Conditional ordering

Suppose you want to order the resultset in such a way that particular number comes first followed by others in ascending order. You can do this via CASE expression in the ORDER BY clause. The flowing always displays 4 on top followed by other numbers ordered in ascending order.

declare @t table(number int)
insert into @t
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5

select number from @t order by case when number=4 then 1 else 2 end, number

The result is


So CASE expression can be effectively used in many cases


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s