In SQL Server, the CASE function verifies the value based on the list of given conditions, then returns one or more results. In this article we will illustrate a number of different uses of this function in different situations.
In SQL Server, the CASE function verifies the value based on the list of given conditions, then returns one or more results. In this article we will illustrate a number of different uses of this function in different situations.
How to use CASE function
Method 1: How to use simple CASE functions
Method 2: Use a simple case function with an ELSE clause
Method 3: Use the CASE function when there are two or more conditions in the list
Method 4: Use CASE function in search
Method 5: Use the CASE function in the ORDER BY clause
Method 6: Use a simple CASE function in the GROUP BY clause
Method 7: Use a simple CASE function in the GROUP BY clause
Method 1: How to use simple CASE functions
This is the most common use of the case function, in which you can create scalar values based on the list of conditions given.
Suppose we have the following table with columns id (employee number), [First name] (name), [Last name] (them) and gender (gender). Now, we want to create a prefix (Mr, Ms) in front of each name, based on the value of Gender column, do the following:
use tempdb go if exists (select * from dbo.sysobjects where id = object_id (N '[emp]') and OBJECTPROPERTY (id, N'IsUserTable ') = 1) drop table [emp] GO create table Emp (id int, [First name] varchar (50), [Last name] varchar (50), gender char (1)) go insert into Emp (id, [First name], [Last name], gender) values (1, 'John', 'Smith', 'm') insert into Emp (id, [First name], [Last name], gender) values (2, 'James', 'Bond', 'm') insert into Emp (id, [First name], [Last name], gender) values (3, 'Alexa', 'Mantena', 'f') insert into Emp (id, [First name], [Last name], gender) values (4, 'Shui', 'Qui', 'f') insert into Emp (id, [First name], [Last name], gender) values (5, 'William', 'Hsu', 'm') insert into Emp (id, [First name], [Last name], gender) values (6, 'Danielle', 'Stewart', 'F') insert into Emp (id, [First name], [Last name], gender) values (7, 'Martha', 'Mcgrath', 'F') insert into Emp (id, [First name], [Last name], gender) values (8, 'Henry', 'Fayol', 'm') insert into Emp (id, [First name], [Last name], gender) values (9, 'Dick', 'Watson', 'm') insert into Emp (id, [First name], [Last name], gender) values (10, 'Helen', 'Foster', 'F') go
Now, create a [Full name] column to determine which prefix should be 'Mr.' or 'Ms.', based on the value in the column Gender.
Select [id],[Full Name] = case Gender when 'm' then 'Mr. '+[First name]+ ' '+[Last name] when 'f' then 'Ms. '+[First name]+ ' '+[Last name] end from Emp
This procedure returns the result as shown below:
id Full Name ----------- ---------------- 1 Mr. John Smith 2 Mr. James Bond 3 Ms. Alexa Mantena 4 Ms. Shui Qui 5 Mr. William Hsu 6 Ms. Danielle Stewart 7 Ms. Martha Mcgrath 8 Mr. Henry Fayol 9 Mr. Dick Watson 10 Ms. Helen Foster
Method 2: Use a simple case function with an ELSE clause
If you add a row with the value NULL to the gender column, you will not see any names returned in the result set.
Insert the following row into the emp table:
use tempdb go insert into Emp (id,[First name],[Last name], gender ) values (11,'Tom','Gabe',NULL) go
Now create a [Full name] column to determine the prefix prefix each name is 'Mr.' or 'Ms.', based on the value in the column Gender:
Select [id],[Full Name] = case Gender when 'm' then 'Mr. '+[First name]+ ' '+[Last name] when 'f' then 'Ms. '+[First name]+ ' '+[Last name] end from Emp
The result returned is as follows:
id Full Name ----- ----------------------- 1 Mr. John Smith 2 Mr. James Bond 3 Ms. Alexa Mantena 4 Ms. Shui Qui 5 Mr. William Hsu 6 Ms. Danielle Stewart 7 Ms. Martha Mcgrath 8 Mr. Henry Fayol 9 Mr. Dick Watson 10 Ms. Helen Foster 11 NULL
However, we need to display the Full Name of the employee, even if there is no value on Gender.
To do this, you need to use the CASE function with the ELSE clause. Execute the query as follows:
Select [id],[Full Name] = case Gender when 'm' then 'Mr. '+[First name]+ ' '+[Last name] when 'f' then 'Mz. '+[First name]+ ' '+[Last name] else [First name]+ ' '+[Last name] end from Emp
The result returned is as follows:
id Full Name ------ --------------------- 1 Mr. John Smith 2 Mr. James Bond 3 Mz. Alexa Mantena 4 Mz. Shui Qui 5 Mr. William Hsu 6 Mz. Danielle Stewart 7 Mz. Martha Mcgrath 8 Mr. Henry Fayol 9 Mr. Dick Watson 10 Mz. Helen Foster 11 Tom Gabe
Method 3: Use the CASE function when there are two or more conditions in the list
In the above two examples, you find that the conditions are either Male, Female, or None. However, in many cases you will need to use multiple conditions, multiple operators at the same time to return a value.
Add the [Marital Status] column to the table and update the value as 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
Suppose we want to display the employee name with the preceding prefix that makes it easy to determine their marital status. Execute the query as follows:
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
The result returned is:
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 Tom Gabe
Method 4: Use CASE function in search
Suppose we have the following table
use tempdb go if exists (select * from dbo.sysobjects where id = object_id (N '[emp]') and OBJECTPROPERTY (id, N'IsUserTable ') = 1) drop table [emp] GO create table Emp (id int, [First name] varchar (50), [Last name] varchar (50), Salary money) go insert into Emp (id, [First name], [Last name], salary) values (1, 'John', 'Smith ', 120000) insert into Emp (id, [First name], [Last name], salary) values (2,' James', 'Bond', 95000) insert into Emp (id, [First name], [Last name ], salary) values (3, 'Alexa', 'Mantena', 200000) insert into Emp (id, [First name], [Last name], salary) values (4, 'Shui', 'Qui', 36000) insert into Emp (id, [First name], [Last name], salary) values (5, 'William', 'Hsu', 39000) insert into Emp (id, [First name], [Last name], salary) values (6, 'Danielle', 'Stewart', 50000) insert into Emp (id, [First name], [Last name], salary) values (7, 'Martha', 'Mcgrath', 400000) insert into Emp ( id, [First name], [Last name], salary) values (8, 'Henry', 'Fayol', 75000) insert into Emp (id, [First name], [Last name], salary) values (9, 'Dick', 'Watson', 91000) insert into Emp (id, [First name], [Last name], salary) values (10, 'Helen', 'Foster', 124000) go
And now I want to create a Tax column based on the following salary
Select [id], [Full Name] = [First name] + [Last name], Salary, Tax = case When salary between 0 and 36000 then Salary * .24 When salary between 36000 and 450000 then Salary * .28 When salary between 45000 and 75000 then Salary * .30 When salary between 75000 and 150000 then Salary * .32 else Salary * .40 end from Emp
Method 5: Use the CASE function in the ORDER BY clause
Suppose we have the table below in Books:
use tempdb go if exists (select * from dbo.sysobjects where id = object_id (N '[Books]') and OBJECTPROPERTY (id, N'IsUserTable ') = 1) drop table [Books] GO create table Books (Bookid int, Title varchar (100), Authorname varchar (100), state char (2)) go into Books (Bookid, Title, Authorname, state) values (1, 'The Third Eye', 'Lobsang Rampa', 'CA') insert into Books (Bookid, Title, Authorname, state) values (2, 'Service Oriented Architecture For Dummies',' Judith Hurwitz ',' NJ ') insert into Books (Bookid, Title, Authorname, state) values (3,' Business Reference for Students and Professionals', 'Ray Myers',' NY ') insert into Books (Bookid, Title, Authorname, state) values (4,' More Java Gems', 'Dwight Deugo', 'FL') insert into Books (Bookid, Title, Authorname, state) values (5, 'Six Sigma Workbook For Dummies',' Craig Gygi ',' FL ') insert into Books (Bookid, Title, Authorname, state) values (6,' Performance Appraisals : How to Achieve Top Results', 'Priscilla A. Glidden', 'NC') insert into Book s (Bookid, Title, Authorname, state) values (7, 'Talent Management: From Competencies to Organizational Performance', 'John Smith', 'FL') insert into Books (Bookid, Title, Authorname, state) values (8, 'Using Unix', 'Howard Johnson', 'CT') insert into Books (Bookid, Title, Authorname, state) values (9, 'Mastering Oracle', 'Erina Zolotrova', 'CT') insert into Books (Bookid, Title, Authorname, state) values (10, 'How to become CEO', 'Olga Zohaskov', 'NY') goes
To query all the values in the table we use the query function below:
Select * from Books
This function will produce the result as shown below
Picture 1 of CASE function in SQL Server (part 1)
Suppose we want to display the entire number of books in the order of states: first is NY, then CA, NJ, CT and FL.
You can do this using the CASE function as shown below:
select Title, Authorname, state from Books order by case when state = 'NY' then 1 when state = 'CA' then 2 when state = 'NJ' then 3 when state = 'CT' then 4 when state = 'FL' then 5 else 6 end
This function will produce the following result:
Title Authorname state ----------------------------------------------- ------------- --------------------- ----- Business Reference for Students and Professionals Ray Myers NY How to become Third Officer Olga Zohaskov NY The Third Eye Lobsang Rampa CA Depties Service Oriented Architecture For Judith Hurwitz NJ Using Unix Howard Johnson CTO Eolotrova CT More Java Gems Dwight Deugo FL Six Sigma Workbook For Dummies Craig FL Talent Management: From Competencies to Organizational Per John Smith FL Performance Appraisals: How to Achieve Top Results Priscilla A. Glidden NC
Method 6: Use a simple CASE function in the GROUP BY clause
Suppose we have the following table
set quoted_identifier off go to use tempdb go if exists (select * from dbo.sysobjects where id = object_id (N '[emp]') and OBJECTPROPERTY (id, N'IsUserTable ') = 1) drop table [emp] GO create table Emp (id int, [First name] varchar (50), [Last name] varchar (50), Salary money, state char (2)) go insert into Emp (id, [First name], [Last name], salary, State) values (1, 'John', 'Smith', 120000, 'WA') insert into Emp (id, [First name], [Last name], salary, State) values (2, 'James',' Bond ', 95000,' OR ') insert into Emp (id, [First name], [Last name], salary, State) values (3,' Alexa ',' Mantena ', 200000,' WY ') insert into Emp ( id, [First name], [Last name], salary, State) values (4, 'Shui', 'Qui', 36000, 'CO') insert into Emp (id, [First name], [Last name], salary, State) values (5, 'William', 'Hsu', 39000, 'NE') insert into Emp (id, [First name], [Last name], salary, State) values (6, 'Danielle', 'Stewart', 50000, 'TX') insert into Emp (id, [First name], [Last name], salary, State) values (7, 'Martha', 'Mcgrath', 400000, 'PA') inser t into Emp (id, [First name], [Last name], salary, State) values (8, 'Henry', 'Fayol', 75000, 'NJ') insert into Emp (id, [First name], [ Last name], salary, State) values (9, 'Dick', 'Watson', 91000, 'NY') insert into Emp (id, [First name], [Last name], salary, State) values (10, 'Helen', 'Foster', 124000, 'AK') go
And now want to create more TimeZone columns based on State (US state)
select id, [First name], [Last name], salary, Timezone = case when state in ('WA', 'OR', 'NE', 'CO') then 'Pacific' when state in ('NY', 'NJ', 'VT', 'ME', 'NH', 'MA', 'RI', 'CT', 'PA', 'DE', 'MD', 'DC', 'VA', 'W' ',' MI ',' IN ',' OH ',' KY ',' NC ',' GA ',' FL ') then' Eastern 'when state in (' MT ',' ID ',' WY ', 'UT', 'CO', 'AZ', 'NM') then 'Mountain' when state in ('ND', 'SD', 'NE', 'KS', 'OK', 'TX', 'MN', 'MN' ',' IA ',' MO ',' AR ',' LA ',' WI ',' IL ',' TN ',' MS ',' AL ') then' Central 'when state in (' AK ') then 'Alaskan' when state in ('HA') then 'Hawaii' end from emp
The above function will produce the following result
id First name Last name salary Timezone ------------------------------------------- --- 1 John Smith 120000.00 Pacific 2 James Bond 95000.00 Pacific 3 Alea Mantena 200000.00 Mountain 4 Shui Qui 36000.00 Pacific 600.00 Central 6 Martha Mcgrath 400000.00 Eastern 8 Henry Fayol 75000.00 Eastern 9 Dick Watson 91000.00 Eastern 10 Helen Foster 124000.00 Alaskan
Now we want to see all the information in the lines with Eastern and Mountain Timezone
select * from (select id, [First name], [Last name], salary, Timezone = case when state in ('WA', 'OR', 'NE', 'CO') then 'Pacific' when state in ( 'NY', 'NJ', 'VT', 'ME', 'NH', 'MA', 'RI', 'CT', 'PA', 'DE', 'MD', 'DC', 'VA' ',' WV ',' MI ',' IN ',' OH ',' KY ',' NC ',' GA ',' FL ') then' Eastern 'when state in (' MT ',' ID ', 'WY', 'UT', 'CO', 'AZ', 'NM') then 'Mountain' when state in ('ND', 'SD', 'NE', 'KS', 'OK', 'TX' ',' MN ',' IA ',' MO ',' AR ',' LA ',' WI ',' IL ',' TN ',' MS ',' AL ') then' Central 'when state in ( 'AK') then 'Hawaii' end from emp) as mytype where TimeZone in ('Mountain', 'eastern')
The result of this function is as follows
id First name Last name salary Timezone 3 Alexa Mantena 200000.00 Mountain 7 Martha Mcgrath 400000.00 Eastern 8 Henry Fayol 75000.00 Eastern 9 Dick Watson 91000.00 Eastern
Now we have the table above and you want to display the average value of the salary based on the time zone (Timezone).
select avg (salary) as AverageSalary, Timezone = case when state in ('WA', 'OR', 'NE', 'CO') then 'Pacific' when state in ('NY', 'NJ', 'VT' , 'ME', 'NH', 'MA', 'RI', 'CT', 'PA', 'DE', 'MD', 'DC', 'VA', 'WV', 'MI', ' IN ',' OH ',' KY ',' NC ',' GA ',' FL ') then' Eastern 'when state in (' MT ',' ID ',' WY ',' UT ',' CO ' , 'AZ', 'NM') then 'Mountain' when state in ('ND', 'SD', 'NE', 'KS', 'OK', 'TX', 'MN', 'IA', ' MO ',' AR ',' LA ',' WI ',' IL ',' TN ',' MS ',' AL ') then' Central 'when state in (' AK ') then' Alaskan 'when state in ('HA') then 'Hawaii' end from emp group by case when state in ('WA', 'OR', 'NE', 'CO') then 'Pacific' when state in ('NY', 'NJ' , 'VT', 'ME', 'NH', 'MA', 'RI', 'CT', 'PA', 'DE', 'MD', 'DC', 'VA', 'WV', ' MI ',' IN ',' OH ',' KY ',' NC ',' GA ',' FL ') then' Eastern 'when state in (' MT ',' ID ',' WY ',' UT ' , 'CO', 'AZ', 'NM') then 'Mountain' when state in ('ND', 'SD', 'NE', 'KS', 'OK', 'TX', 'MN', ' IA ',' MO ',' AR ',' LA ',' WI ',' IL ',' TN ',' MS ',' AL ') then' Central 'when state in (' AK ') then' Alaskan 'when state in (' HA ') then' Hawaii 'end
This function will produce the result as shown below
Now you just want to see the Eastern and Alaskan time zones from the above results. We can use the HAVING clause as shown below
select avg (salary) as AverageSalary, Timezone = case when state in ('WA', 'OR', 'NE', 'CO') then 'Pacific' when state in ('NY', 'NJ', 'VT' , 'ME', 'NH', 'MA', 'RI', 'CT', 'PA', 'DE', 'MD', 'DC', 'VA', 'WV', 'MI', ' IN ',' OH ',' KY ',' NC ',' GA ',' FL ') then' Eastern 'when state in (' MT ',' ID ',' WY ',' UT ',' CO ' , 'AZ', 'NM') then 'Mountain' when state in ('ND', 'SD', 'NE', 'KS', 'OK', 'TX', 'MN', 'IA', ' MO ',' AR ',' LA ',' WI ',' IL ',' TN ',' MS ',' AL ') then' Central 'when state in (' AK ') then' Alaskan 'when state in ('HA') then 'Hawaii' end from emp group by case when state in ('WA', 'OR', 'NE', 'CO') then 'Pacific' when state in ('NY', 'NJ' , 'VT', 'ME', 'NH', 'MA', 'RI', 'CT', 'PA', 'DE', 'MD', 'DC', 'VA', 'WV', ' MI ',' IN ',' OH ',' KY ',' NC ',' GA ',' FL ') then' Eastern 'when state in (' MT ',' ID ',' WY ',' UT ' , 'CO', 'AZ', 'NM') then 'Mountain' when state in ('ND', 'SD', 'NE', 'KS', 'OK', 'TX', 'MN', ' IA ',' MO ',' AR ',' LA ',' WI ',' IL ',' TN ',' MS ',' AL ') then' Central 'when state in (' AK ') then' Alaskan 'when state in (' HA ') then' Hawaii 'end having case when state in (' WA ',' OR ',' NE ', 'CO') then 'Pacific' when state in ('NY', 'NJ', 'VT', 'ME', 'NH', 'MA', 'RI', 'CT', 'PA', 'DE' ',' MD ',' DC ',' VA ',' WV ',' MI ',' IN ',' OH ',' KY ',' NC ',' GA ',' FL ') then' Eastern ' when state in ('MT', 'ID', 'WY', 'UT', 'CO', 'AZ', 'NM') then 'Mountain' when state in ('ND', 'SD', 'NE' ',' KS ',' OK ',' TX ',' MN ',' IA ',' MO ',' AR ',' LA ',' WI ',' IL ',' TN ',' MS ', 'AL') then 'Central' when state in ('AK') then 'Alaskan' when state in ('HA') then 'Hawaii' end in ('Eastern', 'Alaskan')
The result of the function is shown in the following figure:
Method 7: Use a simple CASE function in the GROUP BY clause
Suppose we have the following table
set quoted_identifier off go to use tempdb go if exists (select * from dbo.sysobjects where id = object_id (N '[emp]') and OBJECTPROPERTY (id, N'IsUserTable ') = 1) drop table [emp] GO create table Emp (id int, [First name] varchar (50), [Last name] varchar (50), Salary money, state char (2)) go insert into Emp (id, [First name], [Last name], salary, State) values (1, 'John', 'Smith', 120000, 'WA') insert into Emp (id, [First name], [Last name], salary, State) values (2, 'James',' Bond ', 95000,' OR ') insert into Emp (id, [First name], [Last name], salary, State) values (3,' Alexa ',' Mantena ', 200000,' WY ') insert into Emp ( id, [First name], [Last name], salary, State) values (4, 'Shui', 'Qui', 36000, 'CO') insert into Emp (id, [First name], [Last name], salary, State) values (5, 'William', 'Hsu', 39000, 'NE') insert into Emp (id, [First name], [Last name], salary, State) values (6, 'Danielle', 'Stewart', 50000, 'TX') insert into Emp (id, [First name], [Last name], salary, State) values (7, 'Martha', 'Mcgrath', 400000, 'PA') inser t into Emp (id, [First name], [Last name], salary, State) values (8, 'Henry', 'Fayol', 75000, 'NJ') insert into Emp (id, [First name], [ Last name], salary, State) values (9, 'Dick', 'Watson', 91000, 'NY') insert into Emp (id, [First name], [Last name], salary, State) values (10, 'Helen', 'Foster', 124000, 'AK') go
And now we need to have 6 tables to store employee IDs of different time zones as shown below
if exists (select * from dbo.sysobjects where id = object_id (N '[eastern]') and objectproperty (id, N'isusertable ') = 1) drop table [eastern] go create table eastern (id int) if exists ( select * from dbo.sysobjects where id = object_id (N '[mountain]') and objectproperty (id, N'isusertable ') = 1) drop table [mountain] go create table mountain (id int) if exists (select * from dbo.sysobjects where id = object_id (N '[hawaii]') and objectproperty (id, N'isusertable ') = 1) drop table [hawaii] go create table hawaii (id int) if exists (select * from dbo.sysobjects where id = object_id (N '[central]') and objectproperty (id, N'isusertable ') = 1) [central] drop table created table central (id int) if exists (select * from dbo.sysobjects where id = object_id (N '[alaskan]') and objectproperty (id, N'isusertable ') = 1) drop table [alaskan] go to create alaskan table (id int) if exists (select * from dbo.sysobjects where id = object_id (N '[pacific]') and objectproperty (id, N'isusertable ') = 1) drop table [paci fic] go create table Pacific (id int) go insert into pacific (id) values (1) insert into pacific (id) values (2) insert into mountain (id) values (3) insert into mountain (id) values (4) ) insert into central (id) values (5) insert into central (id) values (6) insert into eastern (id) values (7) insert into eastern (id) values (8) insert into eastern (id) values (9 ) insert into alaskan (id) values (10) go
If you want to know the entire Eastern time zone employee, you will definitely have to execute a simple query statement as shown below.
select e.id, [First Name], [Last name], Salary, State from emp e join eastern ee on e.id = ee.id
The above query statement will return the following result
id First name Last name salary Timezone ------------------------------------------------ 7 Martha Mcgrath 400000.00 PA 8 Henry Fayol 75000.00 NJ 9 Dick Watson 91000.00 NY
So now suppose we need to create a script that allows us to put a time zone into a variable and display the result based on the value of that variable. This can be done using clauses and CASE functions as follows:
declare @group varchar (10) set @ group = 'Pacific' select ee.id, ee. [First Name], ee. [Last Name], Salary, State, @group as TimeZone from emp ee left join mountain m on m. [id] = ee. [id] kết nối không phải alaskan a trên a. [id] = ee. [id] hăm bên trái h trong h. [id] = ee. [id] trái bên ngoài c trên c. [id] = ee. [id] pacific left kết nối p trên p. [id] = ee. [id] trái bên ngoài e vào e. [id] = ee. [id] where ee.id in (case @group when 'Then' Eastern e.id when 'Mountain' Then m.id when 'Pacific' then p.id when 'Alaskan' then a.id when 'Hawaii' Then h.id khi 'Central' Then c.id end)
The above scenario will have the following results:
id First name Last name salary state TimZone ------------------------------------------ ------------- 1 John Smith 120000.00 WA Pacific 2 James Bond 95000.00 OR Pacific
The above script can be written in the following procedure:
create procedure emp_timezone @Zone varchar (10) as select ee.id, ee. [First Name], ee. [Last Name], Salary, State, @Zone as TimeZone from emp ee left join mountain m on m. [id] = ee. [id] kết nối không phải alaskan a trên a. [id] = ee. [id] hăm bên trái h trong h. [id] = ee. [id] trái bên ngoài c trên c. [id] = ee. [id] pacific left kết nối p trên p. [id] = ee. [id] trái bên ngoài e vào e. [id] = ee. [id] where ee.id in (case @Zone when 'Then' Eastern e.id when 'Mountain' Then m.id when 'Pacific' then p.id when 'Alaskan' then a.id when 'Hawaii' Then h.id khi 'Central' Then c.id end)
And execute the above created function with the command:
exec emp_timezone 'Eastern'
The function will produce the result:
id First name Last name salary state TimZone -------------------------------------------------- --- 7 Martha Mcgrath 400000.00 PA Eastern 8 Henry Fayol 75000.00 NJ Eastern 9 Dick Watson 91000.00 NY Eastern