aggregate string concatenation function 1

[jaxtell]

Is there an aggregate function to concatenate strings? If not, can you provide some direction on how to make one? So something like

select agg_concat(first_name, ', '), last_name from my_table group by last_name;

would return something like

John, Bob, Bill         Smith
Mike, Joe, Steve        Jones

 

[SantaMufasa]

Jaxtell,

I am not aware of any built-in Oracle function that behaves in the way you mention, but one could certainly build a user-defined function to emulate that behaviour.

Do you know how to build a user-defined function in Oracle?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jaxtell]

Well, I've got it created and working, with the exception of the delimiter parameter. Here is some code:

/* Formatted on 2008/01/04 13:39 (Formatter Plus v4.8.8) */
CREATE OR REPLACE TYPE cx_string_concat AS OBJECT (
   ps_string_so_far   VARCHAR2 (32767),
   ps_delimiter       VARCHAR2 (10),
                     --if 10 characters isn't enough for a delimeter, too bad
   STATIC FUNCTION odciaggregateinitialize (po_sctx IN OUT cx_string_concat)
      RETURN NUMBER,
   MEMBER FUNCTION odciaggregateiterate (
      self   IN OUT   cx_string_concat,
      val    IN       VARCHAR2
   )
      RETURN NUMBER,
   MEMBER FUNCTION odciaggregateterminate (
      self          IN       cx_string_concat,
      ps_returnvalue   OUT      VARCHAR2,
      pn_flags         IN       NUMBER
   )
      RETURN NUMBER,
   MEMBER FUNCTION odciaggregatemerge (
      self   IN OUT   cx_string_concat,
      po_ctx2   IN       cx_string_concat
   )
      RETURN NUMBER
);

/* Formatted on 2008/01/04 13:43 (Formatter Plus v4.8.8) */
CREATE OR REPLACE TYPE BODY cx_string_concat
IS
   STATIC FUNCTION odciaggregateinitialize (po_sctx IN OUT cx_string_concat)
      RETURN NUMBER
   IS
   BEGIN
      po_sctx := cx_string_concat (NULL, ', ');
      RETURN odciconst.success;
   END;
   MEMBER FUNCTION odciaggregateiterate (
      self   IN OUT   cx_string_concat,
      val    IN       VARCHAR2
   )
      RETURN NUMBER
   IS
   BEGIN
      self.ps_string_so_far := self.ps_string_so_far || self.ps_delimiter || val;
      RETURN odciconst.success;
   END;
   MEMBER FUNCTION odciaggregateterminate (
      self          IN       cx_string_concat,
      ps_returnvalue   OUT      VARCHAR2,
      pn_flags         IN       NUMBER
   )
      RETURN NUMBER
   IS
   BEGIN
      ps_returnvalue := self.ps_string_so_far;
      RETURN odciconst.success;
   END;
   MEMBER FUNCTION odciaggregatemerge (
      self   IN OUT   cx_string_concat,
      po_ctx2      IN       cx_string_concat
   )
      RETURN NUMBER
   IS
   BEGIN
      RETURN odciconst.success;
   END;
END;

CREATE OR REPLACE FUNCTION cx_josh_test (ps_string VARCHAR2)
   RETURN VARCHAR2 AGGREGATE
   USING cx_string_concat;

That all works. But is it possible to make this function take two parameters, so I can determine delimiter at runtime?

 

[SantaMufasa]

Josh,

Can you not say:

CREATE OR REPLACE FUNCTION cx_josh_test
      (ps_string VARCHAR2, [B][I]delim varchar2[/I][/B])
   RETURN VARCHAR2 AGGREGATE
   USING cx_string_concat;

...which allows you to specify any string as the second argument, "DELIM"?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[oradba101]

Greetings,

Or you could use the CAST function.

Regards,

William Chadbourne
Oracle DBA

 

[jaxtell]

Dave,

I tried making a function with two arguments, and received this error:

PLS-00652: aggregate functions should have exactly one argument.

I tried making a function to wrap the original function and alter the delimeter. No luck there either.

PLS-00653: aggregate/table functions are not allowed in PL/SQL scope.

William,
I think that makes sense. I'm going to give it a try.

 

[jaxtell]

It worked! But I'm sure you had no doubt.

CREATE OR REPLACE
TYPE           CX_STRING_DELIM_TYP AS OBJECT (
    ps_string       VARCHAR2(4000),
ps_delimiter     varchar2(10)
    );

I altered the previous type and function to use this type instead of a varchar2. Usage is something like

select cx_josh_test(CX_STRING_DELIM_TYP(cer.first_name,', ')), cer.LAST_NAME  from employees cer group by cer.LAST_NAME;

 

[jimirvine]

looks familiar
1
2
There are others too. Maybe you could tell us where / who you copied your code from? always good to have another resource.

 

[jaxtell]

The example I found wasn't any more complete than the ones you listed. Thats why I tried to be so complete here. But it was from [URL unfurl="true"]http://www.jlcomp.demon.co.uk/faq/agg_fun.html[/url]. One thing to note that I don't remember reading anywhere. The variable that I named self, must be named self. It doesn't work if its not.

 

[jimirvine]

Nice one Jaxtell. Thanks for the link