A List Function for Oracle

SQL Anywhere has a very convenient LIST function for combining rows of data into a single column.

Ex.

SELECT LIST( emp_id, ';' ORDER BY emp_lname ) AS "Sorted IDs"
FROM EMPLOYEE
GROUP BY dept_id

Sorted IDs
160;105;1250;247;266;249;445;…
1039;129;1142;195;667;1162;902;…
1336;879;586;390;757;148;1483;…
1751;591;1062;1191;992;888;318;…
1013;191;750;921;868;1658;703;…

Since I have to move between SQL Anywhere and Oracle quite often, I needed a way to duplicate, as much as possible, this function in Oracle.

Tom’s stringAgg routine is a great start. It is a good example of building a custom aggregate function in Oracle. However, it doesn’t lend itself to specifying the delimiter. Certainly you can use REPLACE, but if your strings contain commas, you’re out of luck.

Here is a solution, built on Tom’s StringAggType, that uses an object to pass in a delimiter. It’s a little clumsy to call, and doesn’t support DISTINCT, but it does work. Suggestions for improvement are welcome.

SQL> select deptid, list(listparm(lname, '|'))
from emp
group by deptid;

DEPTID       LIST(LISTPARM(LNAME,'|'))
----------   --------------------------------------------------------------------------------
10           Jones|Johnson|Hendricks
20           Sewell|Rogers|Yancy
30           Yost|Smith|Carter|Smith
40           Michaels

The Code:

drop type ListType
/

create or replace type ListParm as object
(
   theString varchar2(4000),
   is_Delimiter varchar2(100)
);
/

create or replace type ListType as object
(
   is_Delimiter varchar2(100),
   theString varchar2(4000),

   static function
        ODCIAggregateInitialize(sctx IN OUT ListType )
        return number,

   member function
        ODCIAggregateIterate(self IN OUT ListType ,
                             value IN ListParm )
        return number,

   member function
        ODCIAggregateTerminate(self IN ListType,
                               returnValue OUT  varchar2,
                               flags IN number)
        return number,

   member function
        ODCIAggregateMerge(self IN OUT ListType,
                           ctx2 IN ListType)
        return number
);
/

show errors

create or replace type body ListType
is

    static function ODCIAggregateInitialize(sctx IN OUT ListType)
    return number
    is
    begin
        sctx := ListType( ',', null );
        return ODCIConst.Success;
    end;

    member function ODCIAggregateIterate(self IN OUT ListType,
                                         value IN ListParm )
    return number
    is
    begin
        self.is_Delimiter := value.is_Delimiter;

        self.theString := self.theString || self.is_Delimiter || value.theString;
        return ODCIConst.Success;
    end;

    member function ODCIAggregateTerminate(self IN ListType,
                                           returnValue OUT varchar2,
                                           flags IN number)
    return number
    is
    begin
        returnValue := rtrim( ltrim( self.theString, self.is_Delimiter ), self.is_Delimiter );
        return ODCIConst.Success;
    end;

    member function ODCIAggregateMerge(self IN OUT ListType,
                                       ctx2 IN ListType)
    return number
    is
    begin
        self.theString := self.theString || self.is_Delimiter || ctx2.theString;
        return ODCIConst.Success;
    end;

end;
/

CREATE OR REPLACE FUNCTION List(input ListParm)
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING ListType;
/