Method 3: Usage of CASE functions when there are two or more conditions in the list.
In the two examples above, we saw the conditions were either Male, Female or None. However, there are occasions when you have to use multiple conditions using operators to return one value.
Let us add a column [Marital Status] to this table and update the values as shown below.
use tempdb
go
alter table Emp add [Marital Status] char(1) — S-Single M-Married
go
Update Emp set [Marital Status]=’S’ where id in (1,5,8)
Update Emp set [Marital Status]=’M’ where [Marital Status] is NULL
Go
Let us assume that we want to display the persons name with a prefix that would help us easily identify whether someone is married or single. Execute the following query as shown below.
Select [id],[Full Name] = case
when Gender =’m’ and [marital status] =’S’ then ‘MR. ‘+[First name]+ ‘ ‘+[Last name]
when Gender =’m’ and [marital status] =’M’ then ‘Mr. ‘+[First name]+ ‘ ‘+[Last name]
when Gender =’f’ and [marital status] =’S’ then ‘Ms. ‘+[First name]+ ‘ ‘+[Last name]
when Gender =’f’ and [marital status] =’M’ then ‘Mrs. ‘+[First name]+ ‘ ‘+[Last name]
else [First name]+ ‘ ‘+[Last name]
end
from Emp
This would produce the result shown below.
id Full Name
———– ——————–
1 MR. John Smith
2 Mr. James Bond
3 Mrs. Alexa Mantena
4 Mrs. Shui Qui
5 MR. William Hsu
6 Mrs. Danielle Stewart
7 Ms. Martha Mcgrath
8 MR. Henry Fayol
9 Mr. Dick Watson
10 Mrs. Helen Foster
11 Bill Gates
