How to use CASE function to form conditional expressions?

Introduction
Here I will describe the function in the SQL with conditional expressions or CASE   function in your SQL Query
Execute the following Microsoft SQL Server T-SQL database scripts in Management Studio Query Editor to demonstrate the use of the CASE function to build complex conditional expressions.

-- SQL CASE function in WHERE clause - QUICK SYNTAX

DECLARE @ProductID INT = NULL -- input parameter

SELECT * FROM AdventureWorks2008.Production.Product

WHERE ProductID = CASE

WHEN @ProductID is not null THEN @ProductID

ELSE ProductID END

GO

-- (504 row(s) affected)

DECLARE @ProductID INT = 800 -- input parameter

SELECT * FROM AdventureWorks2008.Production.Product

WHERE ProductID = CASE

WHEN @ProductID is not null THEN @ProductID

ELSE ProductID END

-- (1 row(s) affected)

------------
-- SQL CASE function simple conditional expression syntax
SELECT ProductName=Name,
ListPrice,
Color = CASE WHEN Color is NULL THEN 'N/A'
WHEN Color = 'Multi' THEN 'Mixed'
ELSE Color END
FROM AdventureWorks2008.Production.Product
ORDER BY Color
-- SQL CASE function to avoid divide by zero
SELECT ProductName=Name,
StandardCost,
ListPrice,
CostToPrice = StandardCost /
CASE WHEN ListPrice > 0.0 THEN ListPrice ELSE 1.0 END
FROM AdventureWorks2008.Production.Product
------------

------------

-- CASE function in WHERE clause predicate for complex filtering - CASE function ORDER BY

------------

USE AdventureWorks

GO

CREATE PROCEDURE sprocGetContactInfo

@SortField CHAR(10),

@AscOrDesc CHAR(4),

@Title CHAR(5) = NULL

AS

BEGIN

SET nocount ON

SELECT ContactID,

Title,

FirstName,

LastName,

Phone

FROM Person.Contact

WHERE Title = CASE

WHEN @Title IS NOT NULL THEN @Title

ELSE Title

END

ORDER BY CASE @AscOrDesc -- Complex sorting with CASE

WHEN 'asc' THEN CASE @SortField

WHEN 'FirstName' THEN FirstName

WHEN 'LastName' THEN LastName

END

END ASC,

CASE @AscOrDesc

WHEN 'desc' THEN CASE @SortField

WHEN 'FirstName' THEN FirstName

WHEN 'LastName' THEN LastName

END

END DESC

END

GO

EXEC sprocGetContactInfo 'LastName', 'desc', 'Mr.'

/* ContactID Title FirstName LastName Phone

994 Mr. Michael Zwilling 542-555-0100

988 Mr. Arvid Ziegler 398-555-0100

986 Mr. Frank Zhang 889-555-0100 .... */

EXEC sprocGetContactInfo 'FirstName', 'asc', 'Ms.'

EXEC sprocGetContactInfo 'LastName', 'asc', 'Sr.' ------------

-- SQL CASE function to transpose rows to columns
USE AdventureWorks2008;

SELECT TOP 10

ProductNumber,

Road = CASE ProductLine

WHEN 'R' THEN 'X'

ELSE ''

END,

Mountain = CASE ProductLine

WHEN 'M' THEN 'X'

ELSE ''

END,

Touring = CASE ProductLine

WHEN 'T' THEN 'X'

ELSE ''

END,

OtherItem = CASE ProductLine

WHEN 'R' THEN ''

WHEN 'M' THEN ''

WHEN 'T' THEN ''

ELSE 'X'

END,

ProductName = Name

FROM Production.Product

ORDER BY NEWID();

GO

/* Results

ProductNumber	Road	Mountain	Touring	OtherItem	ProductName
BK-M82S-38		X			Mountain-100 Silver, 38
BK-T18Y-44			X		Touring-3000 Yellow, 44
BK-M68S-46		X			Mountain-200 Silver, 46
MS-2341				X	Metal Sheet 5
BK-T79U-54			X		Touring-1000 Blue, 54
RW-R762	X				ML Road Rear Wheel
FR-M94B-42		X			HL Mountain Frame - Black, 42
HN-1224				X	Hex Nut 7
SH-W890-M		X			Women's Mountain Shorts, M
HB-M243		X			LL Mountain Handlebars

*/

------------

-- T-SQL CASE function - create range descriptions by list price - translate
------------

USE AdventureWorks2008;

SELECT TOP 300

ProductName = Name,

ListPrice,

Color = coalesce(Color,'N/A'),

'Price Category' = CASE

WHEN ListPrice < 25.00 THEN 'Bargain'

WHEN ListPrice < 100.00 THEN 'Inexpensive'

WHEN ListPrice >= 100.00

AND ListPrice <= 1000.00 THEN 'Average'

WHEN ListPrice < 3000.00 THEN 'Expensive'

ELSE 'Very Expensive'

END

FROM Production.Product

WHERE ListPrice > 0

ORDER BY ListPrice DESC,

ProductName

GO

/* Partial results

ProductName	ListPrice	Color	Price Category
HL Touring Frame - Blue, 46	1003.91	Blue	Expensive
HL Touring Frame - Blue, 50	1003.91	Blue	Expensive
HL Touring Frame - Blue, 54	1003.91	Blue	Expensive
HL Touring Frame - Blue, 60	1003.91	Blue	Expensive
HL Touring Frame - Yellow, 46	1003.91	Yellow	Expensive
HL Touring Frame - Yellow, 50	1003.91	Yellow	Expensive
HL Touring Frame - Yellow, 54	1003.91	Yellow	Expensive
HL Touring Frame - Yellow, 60	1003.91	Yellow	Expensive
Road-650 Black, 44	782.99	Black	Average
Road-650 Black, 48	782.99	Black	Average
Road-650 Black, 52	782.99	Black	Average
Road-650 Black, 58	782.99	Black	Average
Road-650 Black, 60	782.99	Black	Average

*/

-- SQL CASE function in GROUP BY

use AdventureWorks2008;

select PriceRange =

case

when SubTotal between 0 and 500 then 'Tiny Order'

when SubTotal between 500.0001 and 1500 then 'Small Order'

when SubTotal between 1500.0001 and 10000 then 'Average Order'

when SubTotal between 10000.0001 and 50000 then 'Large Order'

else 'Big Ticket Order' end,

TotalDollar = SUM (SubTotal),

TotalOrders = COUNT(*)

from Sales.SalesOrderHeader

group by

case

when SubTotal between 0 and 500 then 'Tiny Order'

when SubTotal between 500.0001 and 1500 then 'Small Order'

when SubTotal between 1500.0001 and 10000 then 'Average Order'

when SubTotal between 10000.0001 and 50000 then 'Large Order'

else 'Big Ticket Order' end

order by TotalDollar desc

go

/* Results

PriceRange	TotalDollar	TotalOrders
Big Ticket Order	55875712.17	703
Large Order	36628337.49	1202
Average Order	29122600.13	10677
Small Order	4987315.572	5997
Tiny Order	723214.7526	12886

*/

-- SQL CASE to create PIVOT query / report USE AdventureWorks2008;

SELECT [Year]=YEAR(OrderDate),

SUM(CASE WHEN MONTH(OrderDate) = 1 THEN SubTotal END) AS 'JAN'

,SUM(CASE WHEN MONTH(OrderDate) = 2 THEN SubTotal END) AS 'FEB'

,SUM(CASE WHEN MONTH(OrderDate) = 3 THEN SubTotal END) AS 'MAR'

,SUM(CASE WHEN MONTH(OrderDate) = 4 THEN SubTotal END) AS 'APR'

,SUM(CASE WHEN MONTH(OrderDate) = 5 THEN SubTotal END) AS 'MAY'

,SUM(CASE WHEN MONTH(OrderDate) = 6 THEN SubTotal END) AS 'JUN'

,SUM(CASE WHEN MONTH(OrderDate) = 7 THEN SubTotal END) AS 'JUL'

,SUM(CASE WHEN MONTH(OrderDate) = 8 THEN SubTotal END) AS 'AUG'

,SUM(CASE WHEN MONTH(OrderDate) = 9 THEN SubTotal END) AS 'SEP'

,SUM(CASE WHEN MONTH(OrderDate) = 10 THEN SubTotal END) AS 'OCT'

,SUM(CASE WHEN MONTH(OrderDate) = 11 THEN SubTotal END) AS 'NOV'

,SUM(CASE WHEN MONTH(OrderDate) = 12 THEN SubTotal END) AS 'DEC'

FROM Sales.SalesOrderHeader

GROUP BY YEAR(OrderDate)

ORDER BY [Year]

GO

/* Partial results

Year	JAN	FEB	MAR	APR	MAY	JUN
2002	1453197	2833324	2391929	1724737	3401203	2304183
2003	2021335	3353516	2363458	2752819	4027046	2947980
2004	3340283	4712382	4771753	4274109	5899389	6088719

*/

------------

------------
-- T-SQL PIVOT query using the CASE function - Crosstab query ------------

-- SQL group by aggregate - T-SQL currency conversion

SELECT YEAR = YEAR(OrderDate),

COALESCE('$'+CONVERT(VARCHAR,SUM(CASE

WHEN DATEPART(QQ,OrderDate) = 1 THEN Subtotal

END),1),'') AS 'Q1',

COALESCE('$'+CONVERT(VARCHAR,SUM(CASE

WHEN DATEPART(QQ,OrderDate) = 2 THEN Subtotal

END),1),'') AS 'Q2',

COALESCE('$'+CONVERT(VARCHAR,SUM(CASE

WHEN DATEPART(QQ,OrderDate) = 3 THEN Subtotal

END),1),'') AS 'Q3',

COALESCE('$'+CONVERT(VARCHAR,SUM(CASE

WHEN DATEPART(QQ,OrderDate) = 4 THEN Subtotal

END),1),'') AS 'Q4'

FROM AdventureWorks2008.Sales.SalesOrderHeader

GROUP BY YEAR(OrderDate)

ORDER BY YEAR(OrderDate)

/*

YEAR Q1 Q2 Q3 Q4

2001 $5,294,961.92 $7,671,148.64

2002 $6,678,449.12 $7,430,122.29 $12,179,372.04 $9,798,486.39

2003 $7,738,309.35 $9,727,845.55 $16,488,806.73 $15,192,201.07

2004 $12,824,418.47 $16,262,217.91 $50,840.63

*/

------------

-- Applying the CASE function for transposing rows into columns

-- Create a vertical test table to be used in the transpose with SELECT INTO

USE tempdb;

SELECT CustomerID,

Label = cast('Name' AS VARCHAR(32)),

VALUE = cast(ContactName AS VARCHAR(64))

INTO NameAddress

FROM Northwind.dbo.Customers

UNION ALL

SELECT CustomerID,

Label = cast('Address' AS VARCHAR(32)),

Address

FROM Northwind.dbo.Customers

UNION ALL

SELECT CustomerID,

Label = cast('CityStateZip' AS VARCHAR(32)),

City + ', ' + isnull(Region,'') + ' ' + PostalCode

FROM Northwind.dbo.Customers

GO

SELECT *

FROM NameAddress

ORDER BY CustomerID,

Label

/* Partial results

CustomerID Label Value

LEHMS Address Magazinweg 7

LEHMS CityStateZip Frankfurt a.M., 60528

LEHMS Name Renate Messner

LETSS Address 87 Polk St. Suite 5

LETSS CityStateZip San Francisco, CA 94117

LETSS Name Jaime Yorres

*/

-- Transpose rows into columns - SELECT from SELECT

SELECT CustomerID,

Name = MIN(Name),

[Address] = MIN(Address),

CityStateZip = MIN(CityStateZip)

FROM (SELECT CustomerID,

Name = CASE Label

WHEN 'Name' THEN VALUE

END,

[Address] = CASE Label

WHEN 'Address' THEN VALUE

END,

CityStateZip = CASE Label

WHEN 'CityStateZip' THEN VALUE

END

FROM NameAddress) x

GROUP BY CustomerID

ORDER BY CustomerID

/* Partial results

CustomerID Name Address CityStateZip

ALFKI Maria Anders Obere Str. 57 Berlin, 12209

ANATR Ana Trujillo Avda. de la Constitución 2222 México D.F., 05021

ANTON Antonio Moreno Mataderos 2312 México D.F., 05023

AROUT Thomas Hardy 120 Hanover Sq. London, WA1 1DP

*/

DROP TABLE NameAddress

GO

------------