select ename from emp;

Ename:
--------
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

SQL> WITH emp AS(
  2  SELECT 'ALLEN' ename FROM dual union ALL
  3  SELECT 'WARD' FROM dual union ALL
  4  SELECT 'JONES' FROM dual union ALL
  5  SELECT 'MARTIN' FROM dual union ALL
  6  SELECT 'BLAKE' FROM dual union ALL
  7  SELECT 'CLARK' FROM dual union ALL
  8  SELECT 'SCOTT' FROM dual union ALL
  9  SELECT 'KING' FROM dual union ALL
 10  SELECT 'TURNER' FROM dual union ALL
 11  SELECT 'ADAMS' FROM dual union ALL
 12  SELECT 'JAMES' FROM dual union ALL
 13  SELECT 'FORD' FROM dual union ALL
 14  SELECT 'MILLER' FROM dual)
 15  SELECT LISTAGG(ename, ' , ') WITHIN GROUP (ORDER BY ename) AS employees
 16  FROM   emp;
 
EMPLOYEES
--------------------------------------------------------------------------------
ADAMS , ALLEN , BLAKE , CLARK , FORD , JAMES , JONES , KING , MARTIN , MILLER ,

SQL> with names as (select ename, row_number() over(order by ename) rn from emp)
  2  select substr(sys_connect_by_path(ename,','),2) names
  3  from names
  4  where connect_by_isleaf = 1
  5  connect by prior rn = rn - 1
  6  start with rn = 1
  7  /
NAMES
-------------------------------------------------------------------------------------
ADAMS,ALLEN,BLAKE,CLARK,FORD,JAMES,JONES,KING,MARTIN,MILLER,SCOTT,SMITH,TURNER,WARD

SQL> select listagg(ename,',') within group (order by ename) names from emp;
NAMES
-----------------------------------------------------------------------------------
ADAMS,ALLEN,BLAKE,CLARK,FORD,JAMES,JONES,KING,MARTIN,MILLER,SCOTT,SMITH,TURNER,WARD

SQL> WITH emp AS(
  2  SELECT 'ALLEN' ename FROM dual union ALL
  3  SELECT 'WARD' FROM dual union ALL
  4  SELECT 'JONES' FROM dual union ALL
  5  SELECT 'MARTIN' FROM dual union ALL
  6  SELECT 'BLAKE' FROM dual union ALL
  7  SELECT 'CLARK' FROM dual union ALL
  8  SELECT 'SCOTT' FROM dual union ALL
  9  SELECT 'KING' FROM dual union ALL
 10  SELECT 'TURNER' FROM dual union ALL
 11  SELECT 'ADAMS' FROM dual union ALL
 12  SELECT 'JAMES' FROM dual union ALL
 13  SELECT 'FORD' FROM dual union ALL
 14  SELECT 'MILLER' FROM dual)
 15  SELECT RTRIM(XMLAGG(XMLELEMENT(E, ENAME || ',')).EXTRACT('//text()'), ',') ENAMES
 16    FROM EMP;
 
ENAMES
--------------------------------------------------------------------------------
ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER

CREATE OR REPLACE FUNCTION rowtocol
    ( p_slct IN VARCHAR2,
     p_dlmtr IN VARCHAR2 DEFAULT ',' ) RETURN VARCHAR2

     AUTHID CURRENT_USER AS
     TYPE c_refcur IS REF CURSOR;
     lc_str VARCHAR2(4000);
     lc_colval VARCHAR2(4000);
     c_dummy c_refcur;
     l number;
     BEGIN
     OPEN c_dummy FOR p_slct;
     LOOP
     FETCH c_dummy INTO lc_colval;
     EXIT WHEN c_dummy%NOTFOUND;
     lc_str := lc_str || p_dlmtr || lc_colval;
     END LOOP;
     CLOSE c_dummy;
     RETURN SUBSTR(lc_str,2);
     EXCEPTION
     WHEN OTHERS THEN
        lc_str := SQLERRM;
        IF c_dummy%ISOPEN THEN
        CLOSE c_dummy;
     END IF;
     RETURN lc_str;
     END;

select rowtocol('select ename from emp',',')from dual;