我想在我的查询中添加一列，它将指定一行匹配的一个或多个类别。我想接受这个：
+--------------+---------------+
Row | Product | Quantity_Sold |
+--------------+---------------+
1 | Coca-Cola | 15 |
2 | Cigarettes | 4 |
3 | Pretzel | 6 |
4 | Beer | 25 |
5 | Popcorn | 10 |
6 | Candy Bar | 10 |
+--------------+---------------+
并返回：
+--------------+---------------+----------------------+
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 |
+--------------+---------------+----------------------+
请注意输出的第 4-5 行，“Beer”位于两行，因为它属于两个类别。
如果我尝试使用 CASE 执行此操作，则只会计算第一个匹配项。
这个查询
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;
只会返回这个输出
+--------------+---------------+----------------------+
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 |
+--------------+---------------+----------------------+
（注意“啤酒”只出现一次）
那么我怎样才能让它在它匹配的所有类别的单独行上显示呢？