title: LISTAGG: group within group
body: <p>Let me start with data to better describe what I need. I have a table called SUPERMARKET with the following fields: </p>
  
  <pre><code>Field 1: StoreID
  Field 2: ProductCategory
  Field 3: ProductID
  </code></pre>
  
  <p>The data will look like this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>And I would like to have a view like this:</p>
  
  <pre><code>1, Fruit:(Banana, PineApple, Strawberry), Beverage:(Milk, Chocolate Milk, Apple Juice), Vegetable: (beet)
  2, Vegetable:(beet, onion, kyle)
  </code></pre>
  
  <p>Does Oracle have a way to display the information I was looking for as described above? I have tried:</p>
  
  <pre><code>SELECT "StoreID", LISTAGG("ProductCategory",',') WITHIN GROUP (ORDER BY "ProductCategory") "ProductCategories" FROM SUPERMARKET GROUP BY "StoreID"
  </code></pre>
  
  <p>But this one only lists:</p>
  
  <pre><code>1, "Fruit,Beverage,Vegetable"
  2, "Vegetable"
  </code></pre>
  
  <p>or if I use ProductID instead of ProductCategory then I get a list of product displayed randomly and not grouped by category</p>
  
  <pre><code>SELECT "StoreID", LISTAGG("ProductID",',') WITHIN GROUP (ORDER BY "ProductID") "Products" FROM SUPERMARKET GROUP BY "StoreID"
  </code></pre>
  
  <p>Does anyone have a trick how to solve this? Please help.</p>
  
  <p><strong><em>UPDATE AND ISSUE WITH VIEW:</em></strong></p>
  
  <p>The sql suggested by everyone works like a charm until I tried to put the exact same working sql into a view. For some reason, Oracle compiler does not like it and throws out error:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Does anyone knows why? Since it is related to my original question, I thought I would put it in the same question for easy reference in future.</p>
  
  <p><img src="https://i.stack.imgur.com/6AtlI.png" alt="enter image description here"></p>
  
  <p><strong><em>Suggestion from Gordon</em></strong>: </p>
  
  <p>This is actually a bug from SQL Developer GUI. To bypass the issue ->Create view using statement .</p>