Return multiple rows from matching on multiple CASE matches

case

I want to add a column to my query which will specify one or more categories a row matches. I want to take this:

    +--------------+---------------+
Row | Product      | Quantity_Sold |
    +--------------+---------------+
1   | Coca-Cola    | 15            |
2   | Cigarettes   | 4             |
3   | Pretzel      | 6             |
4   | Beer         | 25            |
5   | Popcorn      | 10            |
6   | Candy Bar    | 10            |
    +--------------+---------------+

And return this:

    +--------------+---------------+----------------------+
Row | Product      | Quantity_Sold | Category             |
    +--------------+---------------+----------------------+
1   | Coca-Cola    | 15            | Beverages            |
2   | Cigarettes   | 4             | Controlled Substance |
3   | Pretzel      | 6             | Snacks               |
4   | Beer         | 25            | Beverages            |
5   | Beer         | 25            | Controlled Substance |
6   | Popcorn      | 10            | Snacks               |
7   | Candy Bar    | 10            | Snacks               |
    +--------------+---------------+----------------------+

Notice on line 4-5 of the output, "Beer" is on two lines, because it fits in two categories.

If I try to do this with CASE, only the first match will be counted.

This query

SELECT
    Product,
    Quantity_Sold,
    CASE
        WHEN
            Product IN ('Coca-Cola', 'Beer')
        THEN
            'Beverages'
    CASE
        WHEN
            Product IN ('Pretzel', 'Popcorn', 'Candy Bar')
        THEN
            'Snacks'
    CASE
        WHEN
            Product IN ('Cigarettes', 'Beer')
        THEN
            'Controlled Substance'
    END
        AS Category
FROM sales_table;

Would only return this output

    +--------------+---------------+----------------------+
Row | Product      | Quantity_Sold | Category             |
    +--------------+---------------+----------------------+
1   | Coca-Cola    | 15            | Beverages            |
2   | Cigarettes   | 4             | Controlled Substance |
3   | Pretzel      | 6             | Snacks               |
4   | Beer         | 25            | Beverages            |
5   | Popcorn      | 10            | Snacks               |
6   | Candy Bar    | 10            | Snacks               |
    +--------------+---------------+----------------------+

(Notice "Beer" only appears once)

So how can I get it to show up on separate lines for all categories it matches?

Best Answer

A join would be the best solution here.

Create a table that contains a list of categories and which products are associated with them, something like Product, Category we'll call it ProductCategories. Then join on this table.

SELECT p.Product, p.Quantity_Sold, pc.Category
FROM sales_table p
JOIN ProductCategories pc ON pc.Product = p.Product'

This has the added benefit of not having to modify your case statement as you add more categories (which quickly gets difficult/impossible to maintain), and only requires a simple insert into the new table.

Related Question