String aggregation: Oracle equivalent for a MySQL 'GROUP_CONCAT()' 2

[Slippenos]

My company has just made the switch from MySQL to Oracle. One of the issues that I have encountered is the lack of a string aggregation function; in MySQL there is the GROUP_CONCAT().

Basically, it will retrieve a list of all of the pertinent data contained in the selected column.

For instance, in MySQL:

Name   | Age
-------------
Mike   | 12
Tim    | 19
Sal    | 11

SELECT GROUP_CONCAT(Age) AS AgeList
FROM aboveTable;

AgeList
--------
11,12,19

This also automatically orders the column.

-Fine, back to Oracle ...

I found this excellent function on the web to and merely renamed the function 'GROUP_CONCAT'. However, with the same table and query- I get the following results:

AgeList
--------
12,19,11

The function pulls the data out of the table exactly as it is ordered. Unfortunately, order is important and I have to have this list ordered in the same fashion as the MySQL function does.

Here is the link for the function I created:

http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php

And here is the code:

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

I am unfamiliar with writing custom functions inside the dbms; let alone for Oracle.

Any thoughts or suggestions on how I can best achieve an ordered list based on the code above? Any input is greatly appreciated.

Thanks in advance,
Mike



[blue]Go to work to learn. Don't go to work to earn.[/blue]

 

[oradba101]

Hi, Mike

Try thid

code

WITH temp_table
AS
(SELECT *
FROM aboveTable
ORDER BY age
)
SELECT GROUP_CONCAT(Age) AS AgeList
FROM temp_table;

AgeList
--------
11,12,19

/code

Regards,


William Chadbourne
Oracle DBA

 

[Slippenos]

Thanks, oradba101. Thats exactly what I was looking for.

[blue]Go to work to learn. Don't go to work to earn.[/blue]

 

[guelphdad]

Slippenos, just an FYI on the GROUP_CONCAT you've used above, in case it comes up somewhere else. It does not order the column. If that happened it is by coincidence. only an ORDER BY clause can order a column.

 

[SantaMufasa]

Try this code...SELECT GROUP_CONCAT(Age)...

William, I didn't see a clarification of what venue your code will work. Could you please clarify for future readers of this thread?

Thanks,


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

 

[oradba101]

Hi, Mufasa

It is apparent that the function will group and order the selection in MySQL but will only group in Oracle. So in order to put it in the order that Mike required it would first need to be ordered first and then grouped. The WITH command allows the order to be set in a temporary area which then can be called by the second SELECT statement keeping the required ORDER BY intact.

Is this what you meant by clarification?

Regards,



William Chadbourne
Oracle DBA

 

[SantaMufasa]

William,

"Venue clarification" was, perhaps, a poor choice of terms on my part. My earlier suggestion relates to what I consider to be rather annoying idiosyncracies (or narrow utility, IMHO) of Oracle's "GROUP_CONCAT()" function to get the result that one wants:

1) The function is not available for pre-10g versions.
2) The functions syntactical "venues" (i.e., available coding opportunities where one can use it seem narrower than for other Oracle functions.

To deal with (what I perceive to be) these limitations of Oracle's GROUP_CONCAT, I personally prefer a "home brew" (i.e., user-defined) version that is rearward-version-compatible back to when ref cursors became available:

CREATE OR REPLACE FUNCTION matrix (query_in in VARCHAR2) RETURN VARCHAR2 IS
    incoming    varchar2(4000);
    hold_result varchar2(4000);
    c sys_refcursor;
Begin
    open c for query_in;
    loop
        fetch c into incoming;
        exit when c%notfound;
        hold_result := hold_result||','||incoming;
    end loop;
    return ltrim(hold_result,',');
END;
/

Function created.

Then, Slippenos could use the above function in any version and expression venue where a character expression is legal, sorted any way he wants:

select matrix('select Age from AboveTable order by 1') AgeList from dual;

AGELIST
--------
11,12,19

Because of the simplicity of the above application, the use may seem trivial (since we have only one row of output and need drive the query only with the "dual" table), but the function's qualities of simplicity, I believe, are more evident with a more complex example:

col workers format a50
select dept_id
      ,matrix('select last_name from s_emp where dept_id = '||dept_id)
          as workers
  from s_emp
 group by dept_id;

DEPT_ID WORKERS
------- --------------------------
     10 Quick-To-See
     31 Nagayama,Magee
     32 Giljum
     33 Sedeghi
     34 Nguyen,Patel
     35 Dumas
     41 Ngao,Urguhart,Maduro,Smith
     42 Menchu,Nozaki,Patel
     43 Biri,Newman,Markarian
     44 Catchpole,Chang
     45 Havel,Dancs,Schwartz
     50 Velasquez,Ropeburn

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

 

[oradba101]

Hi, Mufasa

Your function looks great. And it certainly is a lot easier to understand than the GROUP_CONCAT function being used by Mike. But I was just providing a "quick" way for him to be able order the records the way he needed them to be.

Regards,



William Chadbourne
Oracle DBA

 

[Slippenos]

SantaMufasa,
I agree your function does look great. My first thought was to use the elaborate GROUP_CONCAT function I posted above as there would be little to no code change during our migration.
However, the function you created above gives me a bit more piece of mind as it achieves the same result with less [more understandable] code.
As an Oracle novice; I have to walk before I can run- so thanks for your input- its a great technique. Thanks to anyone else who has contributed to this thread.


[blue]Go to work to learn. Don't go to work to earn.[/blue]