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;
/

Advertisements
Published in: on January 17, 2008 at 5:22 pm  Comments (11)  

The URI to TrackBack this entry is: https://dmcmeans.wordpress.com/2008/01/17/a-list-function-for-oracle/trackback/

RSS feed for comments on this post.

11 CommentsLeave a comment

  1. Hi,

    The List function you used in this is 10g supported or even for 9i, Because when i am trying to use this i am getting a error

    ERROR at line 1:
    ORA-00904: “LIST”: invalid identifier

    or if the function is user created please send me the syntax of that function

    Thanks
    Sreehasrha Boneni

  2. Ooops. Sorry, I forgot the List function definition. I’ve added it at the end of the code. Thanks for the catch.

  3. I did not understand how to use the order by in list.
    Please explain.

  4. Hi
    i want to know about how stringagg() is used in SQL statement,Plz give me a detail clarification.

  5. StringAgg() was written by Tom Kyte. Search for string_agg or stringagg at asktom.oracle.com.

  6. This is really helpfull.. thnx buddy

  7. Not working

  8. Hi there just wanted to give you a brief heads up and let you know a few of
    the pictures aren’t loading correctly. I’m not sure why but I think its a linking issue.
    I’ve tried it in two different internet browsers and both show the same results.

  9. Bartfield says to eat breakfast every day within an hour of getting out of bed.
    It can help to recall that hypnosis is not being
    done TO you, but what you are choosing to do. For convenience,I just eat
    the same foods everyday.

  10. Superb blog!Do you have any recommendations for aspiring writers?
    I’m hoping to start my own site soon but I’m a little lost on everything.
    Would you suggest starting with a free platform like Wordprdess or go for a paid option?
    There are so many options out there tat I’m totally confused
    .. Any tips? Many thanks!

  11. I don’t know whether it’s just me or if everyone else experiencing issues
    with your site. It appears as though some of the written text in your posts
    are running off the screen. Can somebody else please provide feedback and let
    me know if this is happening to them as well? This
    could be a problem with my web browser because I’ve had this happen before.
    Many thanks


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: