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  = CASE
                          WHEN  THEN 
                          WHEN  THEN 
                          ELSE 
                        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.

Last updated 22 May 2024
Return to the Artful SQL Server & Access Tips page