The CASE statement is a very flexible tool. Here are just a few of the tricks
you can work with it.
Item translation
One of the simplest things you can do is to derive a columns contents based
on it’s contents, or the contents of another data item.
SELECT pub_name,
CASE WHEN state is NULL or state = ''
THEN 'Not supplied'
ELSE state
END
FROM publishers
SELECT CASE WHEN datepart (dw, getdate()) In (1, 7)
THEN 'Weekend overtime rates apply :-)'
ELSE 'Standard rates apply'
END
Titling subtotals
You can use CUBE and ROLLUP to produce totals and subtotals within a result
set, but because SQL Server places NULLS for non-totaled columns in the total
rows, they do not always look very pretty. In this example we use the CASE
statement to place emphasis on the subtotal lines.
SELECT CASE GROUPING(stor_name)
WHEN 1 then '[State total]'
ELSE stor_name
End as store,
state,
sum(qty)
FROM stores s
JOIN sales sl on s.stor_id = sl.stor_id
GROUP BY stor_name, state
WITH CUBE
HAVING GROUPING(state) = 0
ORDER BY state, GROUPING(stor_name)
Simplifying Output
This batch lists of sales by store, and gives a general indication of the
stores performance against the average quantity of sales per store.
declare @storeAvg int
select @storeAvg = avg(storeTotal)
from (
select stor_id,
sum(qty) as storeTotal
from sales
group by stor_id
) as derived
select stor_name,
storeTotal,
case when storeTotal > @storeAvg * 1.1
then 'Above average'
when storeTotal < @storeAvg * 0.9
then 'Below average'
else 'About average'
end as storePerformance
from (
select stor_id,
sum(qty) as storeTotal
from sales
group by stor_id
) as derivedStores
join stores
on stores.stor_id = derivedStores.stor_id
This query batch starts by calculating the average of total sales per store,
which is stored in the variable @storeAvg, then that average is used to rate the
stores sales performance.
Grouping data into ranges
This example calculates the number of orders in certain size ranges. It also
shows off the power of SQL as a set-based language: to code this as in a
procedural language like VB would not be able to do this in a single statement.
SELECT SUM (CASE when qty <= 5 then 1 else 0 end)
as '1_to_5',
SUM (CASE when qty between 6 and 10 then 1 else 0 end)
as '6_to_10',
SUM (CASE when qty between 11 and 20 then 1 else 0 end)
as '11_to_20',
SUM (CASE when qty > 20 then 1 else 0 end)
as '20_plus'
FROM sales
Complex Sequencing
CASE statements can be used in an ‘ORDER BY clause to order non-sequential
data. This example sorts stores by name, but puts all the stores in California
at the top of the list.
SELECT stor_name, state
FROM stores
ORDER BY CASE state
WHEN 'CA' then 1
ELSE 2
END,
stor_name
Further reading
This article discusses how to increase performance of complex
UPDATE statements by using CASE.
This SQL 2000 article contains a section that shows how to use the CASE
statement in a User
Defined Function to enforce complex declarative constraints
About the author
Neil Boyle is an independent SQL Server consultant working out of London,
England. Neil's free SQL Server guide is available on-line at http://www.impetus-sql.co.uk
