Many uses of CASE

from the Artful SQL Server & Access Tips List


CASE may be one of SQL's most underused keywords. It's often used to customize a query column:

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

Substitute literal arguments for the tokens in angle brackets, for 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

But CASE has more uses, for example 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

or in an ORDER BY clause...

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

Notice that this query has to repeat the CASE statement in the SELECT block so it the custom column be used in the GROUP BY block.

With a little forethought, CASE helps you deliver grouped and ordered result sets that you might have thought impossible.



Return to the Artful SQL Server & Access Tips page