Oracle: Function to concatenate column output

 Type:

create or replace
TYPE        "STRINGAGGTYPE"                                          as object
    (
       theString varchar2(4000),
   
       static function
            ODCIAggregateInitialize(sctx IN OUT StringAggType )
            return number,
   
       member function
           ODCIAggregateIterate(self IN OUT StringAggType ,
                                value IN varchar2 )
           return number,
  
      member function
           ODCIAggregateTerminate(self IN StringAggType,
                                  returnValue OUT  varchar2,
                                  flags IN number)
           return number,
  
      member function
           ODCIAggregateMerge(self IN OUT StringAggType,
                              ctx2 IN StringAggType)
           return number
   );

Function:

create or replace
FUNCTION stringAgg(input varchar2 )
    RETURN varchar2
    PARALLEL_ENABLE AGGREGATE USING StringAggType;

Now, you can run a query like this:

select deptno, stringAgg(ename) enames from emp group by deptno;

Result:

DEPTNO  ENAMES
----------  ------------------------------
        10  CLARK,KING,MILLER
        20  SMITH,FORD,ADAMS,SCOTT,JONES
        30  ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD