LISTAGG: 分组内的分组

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

我希望能够拥有这样的一个视图：

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的一个错误。要绕过此问题->使用语句创建视图。

- user1205746

---

一个小工具会很有帮助。 - Mihai

使用语句创建视图对我很有用。UI方式没有起作用。 - intA

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;

- Gordon Linoff

---

您建议的 SQL 在单独执行时非常完美... 但当我将其放入视图中时，Oracle 编译器会产生奇怪的错误。难道我们不能在视图中使用 listagg 吗？ - user1205746

@user1205746……我刚在Oracle中创建了一个，它运行得很好。（我不认为会有任何限制。） - Gordon Linoff

好的，Gordon。我会再试一次，看看我可能错过了什么。在我向你寻求帮助之前，我已经尝试了多次... 嗯... - user1205746

是的.. 这对我来说肯定是个问题。不确定为什么。我已经用屏幕截图更新了问题。很奇怪... - user1205746

Answer 2

SELECT storeid,
  listagg(a,',') within GROUP (
ORDER BY a)
FROM
  (SELECT storeid,
    productCategory
    ||'('
    ||listagg(productId,',') within GROUP (
  ORDER BY productId)
    ||')' a
  FROM supermarket
  GROUP BY storeid,
    productCategory
  )
GROUP BY storeid

- Kukushkin