INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

help with loop in PL/SQL

help with loop in PL/SQL

(OP)
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

RE: help with loop in PL/SQL


Try this:

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close