Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

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

Slippenos (MIS)
3 Jan 07 17:30
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:

CODE

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:

CODE

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:

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

Go to work to learn. Don't go to work to earn.

Helpful Member!  oradba101 (MIS)
4 Jan 07 5:49
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 (MIS)
4 Jan 07 9:46
Thanks, oradba101.  Thats exactly what I was looking for.

Go to work to learn. Don't go to work to earn.

guelphdad (TechnicalUser)
4 Jan 07 13:57
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.
Helpful Member!  SantaMufasa (TechnicalUser)
4 Jan 07 14:52

Quote (William Chadbourne):

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,

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

oradba101 (MIS)
4 Jan 07 15:32
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 (TechnicalUser)
4 Jan 07 17:45
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:

CODE

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:

CODE

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:

CODE

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

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

oradba101 (MIS)
5 Jan 07 3:01
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 (MIS)
5 Jan 07 8:51
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.

Go to work to learn. Don't go to work to earn.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close