help with loop in PL/SQL

[cuetzpalin]

Hi,

Thank for taking time to read my thread. I'm new to PL/SQL so I need some help. I don't know if a loop will work or what to use to accomplish what I need.
I have a table with product codes that can be associated with more than one product category.

What I'm looking to do is create code that will look at all the product codes and basically loop through the table and create a new table with all the associated product categories for a particular product code.

Here's an example of the data:

Product code:
A145041
Category: Filters
A452002
Category: Hardware
A145041
Category: Hardware
A245666
Category: Film

Product code: A145041 is in multiple Categories: Filters and Hardware.

So, in my final data set table I want to combine the Categories into one data field.

It should look like this:
Product Code: A145041 Category: Filters, Hardware
Product Code: A452002 Category: Hardware
Product Code: A245666 Category: Film

I hope that makes sense.

Thank you so much in advance!
- C

 

[LKBrwnDBA]

Try this:

SQL> WITH Prod_Tab (Product_Code, Category)
  2      AS (SELECT 'A145041', 'Filters' FROM DUAL UNION ALL
  3              SELECT 'A452002', 'Hardware' FROM DUAL UNION ALL
  4              SELECT 'A145041', 'Hardware' FROM DUAL UNION ALL
  5              SELECT 'A245666', 'Film' FROM DUAL)
  6    SELECT Product_Code
  7        , LISTAGG (Category, ', ') WITHIN GROUP (ORDER BY Category) Categories
  8     FROM Prod_Tab
  9* GROUP BY Product_Code
SQL> /

PRODUCT_CODE          CATEGORIES
--------------------- ------------------------------------------------------------
A145041               Filters, Hardware
A245666               Film
A452002               Hardware

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb