How to convert rows into comma separated values in SQL Oracle (4 ways to convert)

I have this requirement many times while coding interfaces in oracle apps. Every time, I use different options.  Here, I tried to list out the four different ways of achieving the same.

Let’s take the well-known SCOTT schema for our example. The requirement is to show the manager name along with sub-ordinate names in a comma separated list.

So first, let us simply join the tables to see the records,

OPTION 1: LISTAGG function ( Tested in Oracle 11g)

SELECT   manager.ename manager,

         LISTAGG(suboridinates.ename,',') WITHIN GROUP (order bysuboridinates.ename) suboridinates_list

    FROM emp suboridinates, emp manager

   WHERE manager.empno = suboridinates.mgr

   GROUP BY  manager.ename

ORDER BY manager.ename;

OPTION 2: WM_CONCAT function (Tested in 11g version)

Caution: This function is undocumented and unsupported by Oracle

SELECT   manager.ename manager,

         wm_concat (suboridinates.ename) suboridinates_list

    FROM emp suboridinates, emp manager

   WHERE manager.empno = suboridinates.mgr

GROUP BY manager.ename

ORDER BY manager.ename;

OPTION 3: XMLAGG and RTRIM Functions

SELECT   manager.ename manager,

         RTRIM (

          XMLAGG (XMLELEMENT (e, suboridinates.ename || ',')).EXTRACT('//text()'),

          ',') suboridinates_list

    FROM emp suboridinates, emp manager

   WHERE manager.empno = suboridinates.mgr

   GROUP BY  manager.ename

ORDER BY manager.ename;

OPTION 4: Always we can go for a custom stored function to satisfy our specific requirement.

CREATE OR REPLACE FUNCTION get_emp_list (pin_mgr IN NUMBER)

   RETURN VARCHAR2

IS

   lv_result_value   VARCHAR2 (2000);

BEGIN

   FOR i IN (SELECT ename

               FROM emp

              WHERE mgr = pin_mgr)

   LOOP

      lv_result_value := lv_result_value || ',' || i.ename;

   END LOOP;

   RETURN LTRIM (lv_result_value, ',');

END;

Source : How to convert rows into comma separated values in SQL Oracle (4 ways to convert)? ~ Oracle Apps Knowledge Sharing