Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 6 down vote accepted

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.

share|improve this answer
    
Yes, that sounds like a simple way to get each match to show up on a separate row. I don't have any write access to this system, so could I create the "ProductCategories" table on-the-fly so I can use it for this join? I was using CASE because it was rather simple to include in the SELECT statement. –  Nathan C. Lee Jul 25 '13 at 18:33
    
@NathanC.Lee: It depends on the database engine you are using. In MSSQL 2008 or later you can use the "values" virtual table constructor - see simple-talk.com/sql/sql-training/… for examples. Similar syntax is available elsewhere to. In MSSQL 2005 and some other systems you don't have the extended values syntax but can construct a virtual table similarly using unioned selects as a subquery or CTE. –  David Spillett Jul 25 '13 at 19:54
    
If you wanted, you could join on what would effectively be a temp table, although I would highly recommend making this an actual table, as it will be easier to add to/expand and reuse later. –  Sethcran Jul 25 '13 at 20:00
    
@DavidSpillett I'm on Oracle - I don't think I have anything like that. Too bad. –  Nathan C. Lee Jul 25 '13 at 20:52
    
@Sethcran I fully agree - but I don't have any write access. –  Nathan C. Lee Jul 25 '13 at 20:52

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;
share|improve this answer
    
Excellent. That didn't quite work as you posted it. I got an "unsupported column aliasing" error. I think it doesn't like the columns being defined in the WITH line, and it yelled at me about not having a FROM with each select, so I had it use "FROM dual", which is some goofy fake table Oracle keeps around for times such as these. –  Nathan C. Lee Jul 26 '13 at 14:37
    
Fair enough. I didn't realize you were using Oracle when I posted the answer :) –  Kenneth Fisher Jul 26 '13 at 14:48

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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.