The CASE keyword may be one of the most
underused keywords in SQL. While you’ve probably used it to create
a column, it has many more uses. For example, you can use CASE in a
WHERE clause.

First, let’s review the syntax for CASE. In a
typical SELECT, it looks similar to this:

SELECT <myColumnSpec> =
CASE
WHEN <A> THEN <somethingA>
WHEN <B> THEN <somethingB>
ELSE <somethingE>
END

You would substitute specific arguments for the
markers in the previous code. Here’s a simple example:

USE pubs

 GO
SELECT
    Title,
    ‘Price Range’ =
    CASE
        WHEN price IS NULL
THEN ‘Unpriced’
        WHEN price < 10
THEN ‘Bargain’
        WHEN price BETWEEN
10 and 20 THEN ‘Average’
        ELSE ‘Gift to
impress relatives’
    END
FROM titles
ORDER BY price
GO

This is the typical use of CASE, but you can do
a lot more with CASE. For example, see how CASE is used in a GROUP
BY clause:

SELECT ‘Number of Titles’, Count(*)
FROM titles
GROUP BY
    CASE
        WHEN price IS NULL
THEN ‘Unpriced’
        WHEN price < 10
THEN ‘Bargain’
        WHEN price BETWEEN
10 and 20 THEN ‘Average’
        ELSE ‘Gift to
impress relatives’
    END
GO

You can even combine these options, adding an
ORDER BY clause, like so:

USE pubs
GO
SELECT
    CASE
        WHEN price IS NULL
THEN ‘Unpriced’
        WHEN price < 10
THEN ‘Bargain’
        WHEN price BETWEEN
10 and 20 THEN ‘Average’
        ELSE ‘Gift to
impress relatives’
    END AS Range,
    Title
FROM titles
GROUP BY
    CASE
        WHEN price IS NULL
THEN ‘Unpriced’
        WHEN price < 10
THEN ‘Bargain’
        WHEN price BETWEEN
10 and 20 THEN ‘Average’
        ELSE ‘Gift to
impress relatives’
    END,
    Title
ORDER BY
    CASE
        WHEN price IS NULL
THEN ‘Unpriced’
        WHEN price < 10
THEN ‘Bargain’
        WHEN price BETWEEN
10 and 20 THEN ‘Average’
        ELSE ‘Gift to
impress relatives’
    END,
    Title
GO

Note that the query above needs to repeat the
CASE block in the SELECT block in order to use it in the GROUP BY
block.

CASE is useful in a variety of situations
beyond merely selecting custom columns. With a little forethought,
you can deliver grouped and ordered result sets that you might have
thought were impossible.

TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!