SQL Server CASE Statement Example

By:   |   Comments (3)   |   Related: 1 | 2 | 3 | 4 | 5 | > TSQL


Problem

While writing T-SQL code, sometimes it is necessary to divide the retrieved data into categories and perform specific operations based on their category. In other words, depending on the values of the result set, different representations or calculations of the results can be needed for each subset within the same query. Choosing different sort orders due to the values category of the results columns or performing special aggregations for each subset of values separately are two such examples. In these cases, the CASE statement can be quite useful for your SQL Server T-SQL logic.

Solution

In this article, we are going to introduce some common tasks where the flexible usage of the SQL Server CASE statement can be essential. We will create a test environment, define sample tasks and provide practical solutions.

Let’s start with creating a test environment.

First, we will create a test database and a couple of tables.

USE master
GO
 
CREATE DATABASE TestDB
GO
 
USE TestDB
GO
 
--Users
CREATE TABLE [dbo].[User](
   [UserID] [int] NOT NULL,
   [Email] [varchar](254) NOT NULL,
   [FirstName] [varchar](40) NOT NULL,
   [LastName] [varchar](40) NOT NULL,
   [BirthDate] [date] NOT NULL,
   [Gender] [char](1) NULL,
   [Age] AS DATEDIFF(YEAR, BirthDate, GETDATE()),
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
   [UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 
ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [CK_User_Gender] CHECK  (([Gender]='M' OR [Gender]='F'))
GO
 
ALTER TABLE [dbo].[User] CHECK CONSTRAINT [CK_User_Gender]
GO
 
--Sessions
CREATE TABLE [dbo].[Session](
   [SessionID] [int] NOT NULL IDENTITY (1,1),
   [UserID] [int] NOT NULL,
   [StartTime] [datetime] NULL,
   [EndTime] [datetime] NULL,
   [Duration] AS DATEDIFF(SECOND, StartTime, ISNULL(EndTime,0)),
 CONSTRAINT [PK_Session] PRIMARY KEY CLUSTERED 
(
   [SessionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 
ALTER TABLE [dbo].[Session]  WITH CHECK ADD  CONSTRAINT [FK_Session_User_UserID] FOREIGN KEY([UserID])
REFERENCES [dbo].[User] ([UserID])
GO
 
ALTER TABLE [dbo].[Session] CHECK CONSTRAINT [FK_Session_User_UserID]
GO

Then we add some test data.

USE TestDB
GO

--Filling the data
--Users
INSERT [dbo].[User] ([UserID], [Email], [FirstName], [LastName], [BirthDate], [Gender]) VALUES (1, N'[email protected]', N'Tom', N'Smith', CAST(N'1982-05-30' AS Date), N'M')
INSERT [dbo].[User] ([UserID], [Email], [FirstName], [LastName], [BirthDate], [Gender]) VALUES (2, N'[email protected]', N'Ann', N'Jackobs', CAST(N'1965-02-02' AS Date), N'F')
INSERT [dbo].[User] ([UserID], [Email], [FirstName], [LastName], [BirthDate], [Gender]) VALUES (3, N'[email protected]', N'Armen', N'Tadevosyan', CAST(N'1988-02-05' AS Date), N'M')
INSERT [dbo].[User] ([UserID], [Email], [FirstName], [LastName], [BirthDate], [Gender]) VALUES (4, N'[email protected]', N'Vahe', N'Hovhannisyan', CAST(N'1985-09-07' AS Date), N'M')
INSERT [dbo].[User] ([UserID], [Email], [FirstName], [LastName], [BirthDate], [Gender]) VALUES (5, N'[email protected]', N'Sergey', N'Gigoyan', CAST(N'1985-05-30' AS Date), N'M')
INSERT [dbo].[User] ([UserID], [Email], [FirstName], [LastName], [BirthDate], [Gender]) VALUES (6, N'[email protected]', N'Sarah', N'Anderson', CAST(N'2000-04-11' AS Date), N'F')
INSERT [dbo].[User] ([UserID], [Email], [FirstName], [LastName], [BirthDate], [Gender]) VALUES (7, N'[email protected]', N'Silvia', N'Sanches', CAST(N'1997-07-16' AS Date), N'F')
INSERT [dbo].[User] ([UserID], [Email], [FirstName], [LastName], [BirthDate], [Gender]) VALUES (8, N'[email protected]', N'Veronica', N'Cruse', CAST(N'1974-11-12' AS Date), N'F')
INSERT [dbo].[User] ([UserID], [Email], [FirstName], [LastName], [BirthDate], [Gender]) VALUES (9, N'[email protected]', N'Tatiana', N'Markova', CAST(N'1962-06-10' AS Date), N'F')
INSERT [dbo].[User] ([UserID], [Email], [FirstName], [LastName], [BirthDate], [Gender]) VALUES (10, N'[email protected]', N'Patrik', N'Clarke', CAST(N'2001-10-22' AS Date), N'M')
 
--Sessions
SET IDENTITY_INSERT [dbo].[Session] ON 
INSERT [dbo].[Session] ([SessionID], [UserID], [StartTime], [EndTime]) VALUES (1, 1, CAST(N'2019-11-28T16:34:28.057' AS DateTime), CAST(N'2019-11-28T16:41:28.057' AS DateTime))
INSERT [dbo].[Session] ([SessionID], [UserID], [StartTime], [EndTime]) VALUES (2, 2, CAST(N'2019-11-28T16:34:47.430' AS DateTime), CAST(N'2019-11-28T16:54:47.430' AS DateTime))
INSERT [dbo].[Session] ([SessionID], [UserID], [StartTime], [EndTime]) VALUES (3, 3, CAST(N'2019-11-28T16:35:12.463' AS DateTime), CAST(N'2019-11-28T16:38:12.463' AS DateTime))
INSERT [dbo].[Session] ([SessionID], [UserID], [StartTime], [EndTime]) VALUES (4, 4, CAST(N'2019-11-27T16:35:25.923' AS DateTime), CAST(N'2019-11-27T16:42:24.926' AS DateTime))
INSERT [dbo].[Session] ([SessionID], [UserID], [StartTime], [EndTime]) VALUES (5, 4, CAST(N'2019-11-27T17:42:24.923' AS DateTime), CAST(N'2019-11-27T17:58:24.923' AS DateTime))
INSERT [dbo].[Session] ([SessionID], [UserID], [StartTime], [EndTime]) VALUES (6, 1, CAST(N'2019-11-27T17:11:24.923' AS DateTime), CAST(N'2019-11-27T17:12:28.953' AS DateTime))
INSERT [dbo].[Session] ([SessionID], [UserID], [StartTime], [EndTime]) VALUES (7, 5, CAST(N'2019-11-26T15:12:24.923' AS DateTime), CAST(N'2019-11-26T15:14:24.915' AS DateTime))
INSERT [dbo].[Session] ([SessionID], [UserID], [StartTime], [EndTime]) VALUES (8, 6, CAST(N'2019-11-26T15:13:24.923' AS DateTime), CAST(N'2019-11-26T15:14:24.953' AS DateTime))
INSERT [dbo].[Session] ([SessionID], [UserID], [StartTime], [EndTime]) VALUES (9, 7, CAST(N'2019-11-25T15:14:24.923' AS DateTime), CAST(N'2019-11-25T15:14:44.925' AS DateTime))
INSERT [dbo].[Session] ([SessionID], [UserID], [StartTime], [EndTime]) VALUES (10, 8, CAST(N'2019-11-24T15:14:25.923' AS DateTime), CAST(N'2019-11-24T15:16:25.913' AS DateTime))
INSERT [dbo].[Session] ([SessionID], [UserID], [StartTime], [EndTime]) VALUES (11, 9, CAST(N'2019-11-24T12:14:25.923' AS DateTime), CAST(N'2019-11-24T12:15:25.923' AS DateTime))
INSERT [dbo].[Session] ([SessionID], [UserID], [StartTime], [EndTime]) VALUES (12, 10, CAST(N'2019-11-28T16:40:12.107' AS DateTime), CAST(N'2019-11-28T16:41:12.107' AS DateTime))
INSERT [dbo].[Session] ([SessionID], [UserID], [StartTime], [EndTime]) VALUES (13, 1, CAST(N'2019-11-28T18:34:28.057' AS DateTime), CAST(N'2019-11-28T18:41:29.051' AS DateTime))
INSERT [dbo].[Session] ([SessionID], [UserID], [StartTime], [EndTime]) VALUES (14, 1, CAST(N'2019-11-25T18:34:28.057' AS DateTime), CAST(N'2019-11-25T18:47:29.047' AS DateTime))
INSERT [dbo].[Session] ([SessionID], [UserID], [StartTime], [EndTime]) VALUES (15, 9, CAST(N'2019-11-27T18:36:28.057' AS DateTime), CAST(N'2019-11-27T18:36:19.059' AS DateTime))
SET IDENTITY_INSERT [dbo].[Session] OFF
GO

We have created two tables in the TestDB database – User and Session, filled with sample data:

database diagram

Now, let’s define our first tasks.

Using the SQL Server CASE statement to define different sort orders for different subsets

Let’s assume that we need to receive information about sessions and only for the current day, it is needed to get sessions ordered by their start time in descending order. For all other days, we need an ascending order.

To solve this task, we can use CASE logic in the ORDER BY clause and get the desired result easily:

USE TestDB
GO
 
SELECT *
FROM [Session]
ORDER BY CASE WHEN DATEDIFF(DAY, CAST(StartTime AS Date),CAST(N'2019-11-28' AS Date))=0 THEN StartTime END DESC,
         CASE WHEN DATEDIFF(DAY, CAST(StartTime AS Date),CAST(N'2019-11-28' AS Date))>0 THEN StartTime END ASC

As we can see, for the current day (2019-11-28) the newer sessions appear first and for other days the older sessions appear first:

query result set

Using the SQL Server CASE statement to define different columns to order for different subsets

Now, we need to get users information and have the result set sorted by the first names for the male users and by the last name for the female users. Again, using the CASE statement in the ORDER BY clause we will define the sort column based on the data in Gender column to solve this task:

USE TestDB
GO
 
SELECT *
FROM [User]
ORDER BY CASE Gender WHEN 'M' THEN FirstName
         ELSE LastName END

In the result set, the male users are sorted by FirstName and the female users are ordered by LastName:

query result set

We can add Gender in the ORDER BY, to get a result which is easier to read:

USE TestDB
GO
 
SELECT *
FROM [User]
ORDER BY Gender, CASE Gender WHEN 'M' THEN FirstName
                 ELSE LastName END

Now, the result is ordered first by gender and then by the first or last name depending on the gender:

query result set

Using the SQL Server CASE statement to get aggregations for subsets in different columns

The CASE statement can be very helpful when there is a need to aggregate the data for different subsets and represent the results for each subset in separate columns. Calculating the number of male and female users in different columns can be a good example of this.

If we use the CASE statement inside the aggregate function, we can easily get the desired result:

USE TestDB
GO
 
SELECT SUM(CASE WHEN Gender='M' THEN 1 ELSE 0 END) AS NumberOfMaleUsers,
       SUM(CASE WHEN Gender='F' THEN 1 ELSE 0 END) AS NumberOfFemaleUsers
FROM [User]

Thus, we have 5 male and 5 female users in our example:

query result set

The next example calculates male and female users sessions for each day:

USE TestDB
GO
 
SELECT CAST(StartTime AS Date) AS [Date],
       SUM(CASE WHEN u.Gender='M' THEN 1 ELSE 0 END) AS NumberOfMaleUsersPerDay,
       SUM(CASE WHEN u.Gender='F' THEN 1 ELSE 0 END) AS NumberOfFemaleUsersPerDay
FROM [Session] s
INNER JOIN [User] u ON u.UserID=s.UserID
GROUP BY CAST(StartTime AS Date)
ORDER BY [Date]

We will receive the following result:

query result set

Using the SQL Server CASE statement to filter the results based on subsets

The CASE statement can be used in the WHERE clause to apply different filters for different subsets of the data. The following query returns only the male users older than 30 years and the female users older than 20 years:

USE TestDB
GO
 
SELECT *
FROM [User]
WHERE (CASE WHEN Gender='M' THEN Age ELSE 0 END) > 30 OR (CASE WHEN Gender='F' THEN Age ELSE 0 END) > 20
query result set

Using the SQL Server CASE statement for UPDATEs

The CASE statement can be also used to define some logic while updating data in tables.

The next example illustrates the simplest usage of this. Let’s assume that we need to change our gender codes from M and F to 1 and 0 correspondingly. Since we have a check constraint on the "Gender" column, we need to drop that before we change the values. 

The following code does the UPDATE with a CASE statement:

USE TestDB
GO
 
ALTER TABLE [User]
DROP CONSTRAINT CK_User_Gender
GO
 
UPDATE [User]
SET Gender = (CASE WHEN Gender='M' THEN '1'
                   ELSE '0' END)
 
SELECT *
FROM [User]
query result set

Conclusion

In addition to its more common usage, the CASE statement can be very useful in adding flexibility to ordering and aggregating data based on subsets which the above examples show.

Next Steps

The links below can be useful to find more information about the described topic:



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Sergey Gigoyan Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Friday, February 21, 2020 - 3:13:17 PM - Thomas Kelley Back To Top (84705)

Some nice tricks, thank you for sharing!  :-)


Tuesday, February 11, 2020 - 10:53:59 AM - Oscar Back To Top (84348)

Nice way to explain these different ways to use the SQL CASE, Thanks


Tuesday, February 11, 2020 - 4:33:31 AM - Venkataraman Back To Top (84329)

Very useful information















get free sql tips
agree to terms