The following
CASE function T-SQL scripts demonstrate:
1. ProductLine single column data pivoted to columns
2. Instant lookup table replacement
3. CASE in GROUP BY
4. PIVOT months for crosstab(matrix) report
5. Crosstab query by year (rows) and by quarter (columns)
6. Transpose rows into columns
-- FIRST EXAMPLE - layout vertical ProductLine across columns
USE AdventureWorks;
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 |
*/
-- SECOND EXAMPLE - instant category lookup table implementation
USE AdventureWorks;
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 |
*/
-- THIRD EXAMPLE - CASE with 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 |
*/
-- FOURTH EXAMPLE - PIVOT report by month
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 |
*/
------------
-- FIFTH EXAMPLE - Crosstab query by year & by month
-- T-SQL pivot 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
*/ ------------
-- SIXTH EXAMPLE - Transpose rows into columns
-- 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 ------------ |