Return multiple rows from matching on multiple CASE matches?

Question

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?

Sethcran · Accepted Answer · 2013-07-25T18:33:35.810

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.

score 2 · Answer 2 · answered Jul 25 '13 at 20:47

Since you can't create a permanent table you could use a CTE and a join.

WITH Category (Product,Category) AS
    (SELECT 'Coca-Cola','Beverages'
        UNION ALL
    SELECT 'Beer','Beverages'
        UNION ALL
    SELECT 'Pretzel','Snacks'
        UNION ALL
    SELECT 'Popcorn','Snacks'
        UNION ALL
    SELECT 'Candy Bar','Snacks'
        UNION ALL
    SELECT 'Cigarettes','Controlled Substance'
        UNION ALL
    SELECT 'Beer','Controlled Substance'
        )
SELECT
    sales_table.Product,
    Quantity_Sold,
    Category.Category
FROM sales_table
JOIN Category
    ON sales_table.Product = Category.Product;

score 1 · Answer 3 · edited May 23 '17 at 12:40

Thank you, Sethcran and Kenneth Fisher, for each providing parts of this answer. I found the last missing piece here: Oracle create table using with clause (Stack Overflow)

Here's what eventually worked:

WITH Category AS
    (SELECT 'Coca-Cola' AS Product,'Beverages' AS Category
        FROM dual
        UNION ALL
    SELECT 'Beer','Beverages'
        FROM dual
        UNION ALL
    SELECT 'Pretzel','Snacks'
        FROM dual
        UNION ALL
    SELECT 'Popcorn','Snacks'
        FROM dual
        UNION ALL
    SELECT 'Candy Bar','Snacks'
        FROM dual
        UNION ALL
    SELECT 'Cigarettes','Controlled Substance'
        FROM dual
        UNION ALL
    SELECT 'Beer','Controlled Substance'
        FROM dual
    )
SELECT
    sales_table.Product,
    sales_table.Quantity_Sold,
    Category.Category
FROM sales_table
JOIN Category
    ON sales_table.Product = Category.Product;

Return multiple rows from matching on multiple CASE matches?

3 Answers3