Custom function string aggregation in sequence PLSQL [duplicate]

Question

This is a custom function to aggregate strings in oracle.

 Date Fruit   Number
1      Apple    1
1      Apple    4
1      Apple    3
1      Kiwi     6
1      Kiwi     10

Turns to

Date     Fruit   Number
 1     Apple    1-3-4
 1     Kiwi     6-10

By using

 SELECT Date,fruit, string_agg(number) AS Agg
 FROM   fruit
 GROUP BY Date,fruit

string_agg is a function created by code below

 CREATE OR REPLACE TYPE t_string_agg AS OBJECT
(
  g_string  VARCHAR2(32767),

 STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
 RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                   value  IN      VARCHAR2 )
 RETURN NUMBER,

 MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                     returnValue  OUT  VARCHAR2,
                                     flags        IN   NUMBER)
RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                 ctx2  IN      t_string_agg)
RETURN NUMBER
);
/
SHOW ERRORS


CREATE OR REPLACE TYPE BODY t_string_agg IS
STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
RETURN NUMBER IS
BEGIN
sctx := t_string_agg(NULL);
RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                   value  IN      VARCHAR2 )
RETURN NUMBER IS
BEGIN
SELF.g_string := self.g_string || ',' || value;
RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                     returnValue  OUT  VARCHAR2,
                                     flags        IN   NUMBER)
RETURN NUMBER IS
BEGIN
returnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');
RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                 ctx2  IN      t_string_agg)
RETURN NUMBER IS
BEGIN
SELF.g_string := SELF.g_string || ',' || ctx2.g_string;
RETURN ODCIConst.Success;
END;
END;
/
 SHOW ERRORS


 CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)
 RETURN VARCHAR2
 PARALLEL_ENABLE AGGREGATE USING t_string_agg;
 /
 SHOW ERRORS

But the problems I can't order string in sequence the way I want it to be. How do I modify the custom function code to make the aggregation in a certain sequence like order by number or something?

BTW: I'm version 10g, so I can't use listagg. Can this code be modified in order to order by a certain input? Thanks!

Lukasz Szozda · Accepted Answer · 2015-10-29 20:50:22Z

1

Do not reinvent wheel. Use LISTAGG:

CREATE TABLE tab("Date" INT, Fruit VARCHAR(100), "Number" INT);

INSERT INTO tab  VALUES (1      ,'Apple',    1);
INSERT INTO tab  VALUES (1      ,'Apple',     4);
INSERT INTO tab  VALUES (1      ,'Apple',     3);
INSERT INTO tab  VALUES (1      ,'Kiwi',      6);
INSERT INTO tab  VALUES (1      ,'Kiwi',      10);


SELECT DISTINCT "Date", Fruit,
     LISTAGG ("Number" , '-') 
     WITHIN GROUP (ORDER BY "Date") OVER(PARTITION BY "Date", Fruit)  AS "Number"
FROM tab;

SqlFiddleDemo

Warning:

Keep in mind you need column to ORDER BY. In this case you don't have column with ascending orde and using Date which has all the same values may break if query will read data in different order. You should have something like:

ID Date Fruit   Number
1  1    Apple    1
2  1    Apple    4
3  1    Apple    3
4  1    Kiwi     6
5  1    Kiwi     10

And:

LISTAGG ("Number" , '-') 
WITHIN GROUP (ORDER BY ID ASC) OVER(PARTITION BY "Date", Fruit)  AS "Number"

EDIT:

LISTAGG is available from Oracle 11

edited Oct 29, 2015 at 20:50

answered Oct 29, 2015 at 20:40

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sailormoon Over a year ago

Appreciate Lad, but I'm on version 10g(Listagg not supported)and I do wanna learn how to create customized function.

Lukasz Szozda Over a year ago

@SailorMoon Great, but for future be more specific with tags you use

Sailormoon Over a year ago

yeah thanks! I didn't know I can put 10g there on the tag

Collectives™ on Stack Overflow

Custom function string aggregation in sequence PLSQL [duplicate]

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related