At some point you might want to present different content in query results based on particular conditions.

For example, you might have a table containing information on speciality, Award winning ducks (you never know). Lets say that there is a Ducks table (obviously) with the following columns Name, NumberOfAwards and Category (let us never question what awards they’ve won).

Lets say we run the following T-SQL

SELECT Name, NumberOfAwards, Category
FROM Ducks

This will return something like


Name              NumberOfAwards             Category
-------            -------------------       -----------
Barry               10                       1
Ivan                4                        1
Steve               15                       3
Mandy               12                       2

Lets say that we want to add some additional information to the category. The number code is ok but a textual translation would be useful. The numbered categories are as follows.

Number          Category

1                          Racing

2                         Fancy

3                        Fighting

We want to achieve what is shown below,

Name              NumberOfAwards             Category
-------            -------------------       -----------
Barry               10                       Racing
Ivan                4                        Racing
Steve               15                       Fighting
Mandy               12                       Fancy

To can use a CASE to achieve this very simply. If you’ve used CASE in other programming languages, T-SQL use is not dissimilar – its a ‘switch’ used to perform behaviour based on certain conditions.

The syntax of CASE is simple – there are two ways of doing it,

METHOD ONE –

SELECT Name,
NumberOfAwards,
CASE Category  -- the column we're 'testing'
WHEN 1 THEN ‘Racing’ -- if the Category column is 1 then print Racing
WHEN 2 THEN ‘Fancy’ -- if the category is 2 then print Fancy
WHEN 3 THEN ‘Fighting’ -- if the category is 3 then print Fighting
ELSE ‘N/A’ -- is the category isn't 1, 2 or 3 print N/A
END as ‘DuckCategory’ -- end the case
FROM Ducks

METHOD TWO –

SELECT Name,
NumberOfAwards,
‘DuckCategory’  as
CASE  -- look no column name!
WHEN category = 1 THEN ‘Racing’ -- if the Category column is 1 then print Racing
WHEN category = 2 THEN ‘Fancy’ -- if the category is 2 then print Fancy
WHEN category = 3 THEN ‘Fighting’ -- if the category is 3 then print Fighting
ELSE ‘N/A’ -- is the category isn't 1, 2 or 3 print N/A
END -- end the case
FROM Ducks

Both methods achieve the same result -where there is a category number a textual description is printed.

Ducktastic.