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?
Ryan ChongCommented:
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

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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Martyn SpencerConsultantCommented:
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 SpencerConsultantCommented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.