By: Sergey Gigoyan | Updated: 2020-02-11 | 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:
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:
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:
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:
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:
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:
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
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]
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 CASE Expression Overview
- Using the CASE expression instead of dynamic SQL in SQL Server
- Using a SQL Server Case Statement for IF/Else Logic
- https://docs.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-ver15
- https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver15
- https://docs.microsoft.com/en-us/sql/t-sql/functions/aggregate-functions-transact-sql?view=sql-server-ver15
About the author
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
Article Last Updated: 2020-02-11
