Clever Geek Handbook

LISTAGG: group within group

Let me start with the data to better describe what I need. I have a table called SUPERMARKET with the following fields:

Field 1: StoreID
Field 2: ProductCategory
Field 3: ProductID

The data will look like this:

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

And I would like to have the following:

1, Fruit:(Banana, PineApple, Strawberry), Beverage:(Milk, Chocolate Milk, Apple Juice), Vegetable: (beet)
2, Vegetable:(beet, onion, kyle)

Does Oracle have a way to display the information I was looking for as described above? I tried:

SELECT "StoreID", LISTAGG("ProductCategory",',') WITHIN GROUP (ORDER BY "ProductCategory") "ProductCategories" FROM SUPERMARKET GROUP BY "StoreID"

But this list includes:

1, "Fruit,Beverage,Vegetable"
2, "Vegetable"

or if I use ProductID instead of ProductCategory then I get a list of products displayed randomly and not grouped by category

SELECT "StoreID", LISTAGG("ProductID",',') WITHIN GROUP (ORDER BY "ProductID") "Products" FROM SUPERMARKET GROUP BY "StoreID"

Does anyone have a trick on how to solve this? Please, help.

UPDATE AND RELEASE WITH A VIEW:

The sql suggested by everyone works like a charm until I tried to put the same working sql in the view. For some reason the Oracle compiler doesn't like it and throws an error:

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

Does anyone know why? Since this is related to my original question, I thought I would put it in the same question for ease of reference in the future.

enter image description here

Suggestion from Gordon :

This is actually a bug in the SQL developer GUI. To work around the issue -> Create view using operator.

+3


source to share


2 answers


Make two levels of aggregation:



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;
+4


source 


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


+1


source






More articles:


All Articles