oracle sql

Hello Experts.
Here's what I would like.   It's probably possible, but you guys can show me.
I have one small table called Food.
It has two columns  food_type and item.
Here are sample entries:

      Food table
Food_type,      item
=========      ====
fruit                   apple
fruit                   mango
fruit                   cherry
grain                  rice
grain                 wheat
grain                  barley

I would like to display my items as follows:

Food_type,     items
=========    =====
fruit                 apple, mango, cherry
grain                rice, wheat, barely

Is it possible to show result like that?  If so
can you please help me to write a script  to display such result.
Thanks a bunch.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
you could try like this:

SELECT Food_type, LISTAGG(item, ', ') WITHIN GROUP (ORDER BY item) items
GROUP BY Food_type
Order By Food_type

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
If the concatenation can exceed 4000 characters, you can use the XMLAGG trick shown here:

Change .getstringval(), to .getclobval(),
Mark GeerlingsDatabase AdministratorCommented:
I expect that the suggestion to use LISTAGG will give you about what you are asking for.  But I expect the order of the items then will be alphabetical like this:

Food_type,     items
=========    =====
fruit                apple, cherry, mango
grain               barley, rice, wheat

which is not exactly what you had asked for.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Martyn SpencerSoftware Developer / Linux System Administrator / Managing DirectorCommented:
Not an answer to your question, and you therefore may already understand what I am about to say. It is, hopefully, something that you may find helpful. Your food table is not fully normalised, as the "fruit type" column contains multiple repeating attributes. It's perfectly OK to design a database this way if you appreciate the ramifications, but you may want to consider normalising the data further.
Mark GeerlingsDatabase AdministratorCommented:

I don't see a "fruit_type" column in the table.  I see a "food_type" column.  And, I don't see a data normalization problem here.
Martyn SpencerSoftware Developer / Linux System Administrator / Managing DirectorCommented:
Mark: Thanks for the correction - I should have typed "food_type". My comment regarding normalisation should have had far more qualification and since it risks derailing the OP's actual question, it is probably better that it be ignored.
ALad2005Author Commented:
Thank you everyone for valued suggested.  It really helped me.   You guys are really knowledgeable and fast!
Thanks a bunch
slightwv (䄆 Netminder) Commented:
If you have an answer to your question, please do not forget to close the question by accepting the post or posts that directly led to the answer.

If you need help please review the following.

Single post:

Multiple posts:
ALad2005Author Commented:
Thank you all for your help,
I have closed the question after assigning points.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.