SQL Server CASE Statement Example


By:   |   Updated: 2020-02-11   |   Comments (3)   |   Related: More > T-SQL

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:



Last Updated: 2020-02-11


get scripts

next tip button



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.

View all my tips




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

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


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

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

Very useful information



download


get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools