sql - 利斯塔格 : group within group

Question

让我从数据开始，以便更好地描述我的需求。我有一个名为 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

我想要这样的 View :

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"

有没有人知道如何解决这个问题？请帮忙。

有关 View 的更新和问题:

在我尝试将完全相同的工作 sql 放入 View 之前，每个人建议的 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 的错误。绕过这个问题 ->Create view using statement 。

Answer 1

做两层聚合:

SELECT storeId,
       LISTAGG(ProductCategory || ':' || '(' || ProductIds || ')', ', ')
            WITHIN GROUP (ORDER BY ProductCategory) as ProductsAndCategories
FROM (SELECT StoreId, ProductCategory,
             LISTAGG(ProductId, ',') WITHIN GROUP (ORDER BY ProductId) as ProductIds
      FROM SUPERMARKET
      GROUP BY StoreId, ProductCategory
     ) s
GROUP BY StoreId;