How to use CASE function to form conditional expressions?
Execute the following Microsoft SQL Server T-SQL database scripts in Management Studio Query Editor to demonstrate the use of the CASE expression 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
------------
Comments
Post a Comment