让我先用数据更好地描述我需要的内容。我有一个名为SUPERMARKET的表,其中包含以下字段:
Field 1: StoreID
Field 2: ProductCategory
Field 3: ProductID
数据将会长成这个样子:
1, Fruit, Banana
1, Fruit, PineApple
1, Fruit, Strawberry
1, Beverage, Milk
1, Beverage, Chocolate Milk
1, Beverage, Apple Juice
1, Vegetable, beet
2, Vegetable, beet
2, Vegetable, onion
2, Vegetable, Kyle
我希望能够拥有这样的一个视图:
1, Fruit:(Banana, PineApple, Strawberry), Beverage:(Milk, Chocolate Milk, Apple Juice), Vegetable: (beet)
2, Vegetable:(beet, onion, kyle)
Oracle有没有一种方法可以像上面描述的那样显示我要查找的信息?我尝试过以下方法:
SELECT "StoreID", LISTAGG("ProductCategory",',') WITHIN GROUP (ORDER BY "ProductCategory") "ProductCategories" FROM SUPERMARKET GROUP BY "StoreID"
但这个只列出了:
1, "Fruit,Beverage,Vegetable"
2, "Vegetable"
如果我使用ProductID而不是ProductCategory,则会显示产品列表,但是它们不会按类别分组。
SELECT "StoreID", LISTAGG("ProductID",',') WITHIN GROUP (ORDER BY "ProductID") "Products" FROM SUPERMARKET GROUP BY "StoreID"
有没有什么诀窍可以解决这个问题?请帮忙。
更新和视图问题:
每个人建议的SQL都像魔法一样工作,直到我尝试将完全相同的工作SQL放入视图中。由于某种原因,Oracle编译器不喜欢它并抛出错误:
Error(s) parsing SQL:
Unexpected token near *!* in the following:
|| ')', ', ') WITHIN *!*GROUP (
Unexpected token near *!* in the following:
|| ')', ', ') WITHIN GROUP *!*(
Missing expression near *!* in the following:
|| ')', ', ') WITHIN GROUP (
*!*ORDER BY ProductCategory) AS ProductsAndCategories
有人知道原因吗?由于与我的原始问题有关,我想将其放在同一个问题中,以便将来易于参考。
Gordon的建议:
这实际上是SQL Developer GUI的一个错误。要绕过此问题->使用语句创建视图。