SQL: Concatenate values in each result group as a list; LISTAGG and its alternative

For example, you have a product table containing the following columns.

Name                Type           
----------            ------------
Apple                Fruit
Banana              Fruit
Walnut               Nut
Papaya               Fruit
Cashew              Nut

You want your SQL to return a result like this.

Type                   Names
---------              ------------------------------
Fruit                  Apple, Banana, Papaya
Nut                    Cashew, Walnut

1. Find the version of your Oracle database

          select * from v$version;

2. If your Oracle version is 11.2 or later, the Oracle LISTAGG function can help you to achieve your goal.

SQL> select Type, LISTAGG(coalesce( Name, ' '), ',') WITHIN GROUP (ORDER BY Name) as Names
           from product
           group by Type;

The names can be separated by any character by replacing the comma in LISTAGG(Name, ',') with the character.

3. If you use an Oracle database earlier than release 11.2 and LISTAGG is not available, the following shall work for you.

SQL> select Type, rtrim(xmlagg(xmlelement(e, coalesce( Name, ' '), ', ') order by Name).extract('//text()').getclobval(), ', ') AS Names
           from product
           group by Type;
   

--------------------------------------------------------------------------------------------------------------

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.