Defining Aggregate Functions (Concatenation)

[Sarina]

Hi,

I really need an aggregate function that concatenates the values of a column. Supposing that there was such a function and it was called "CAT", I would be able to use it in the fashion described below.

Suppose I had a table called R:

a | b
======
1 | 'anna'
1 | 'jim'
2 | 'john'
2 | 'mary'
2 | 'jane'

Then the query:
select a, CAT(b) as res
from R
group by a

would give me:

a | res
=======
1 | 'anna jim'
2 | 'john mary jane'

I can't figure out how to define the function CAT in a general fashion (for use in the same way that SUM, COUNT, MAX, etc.) are used since I need somehow for the function to recieve as input several rows of a column!

In Sybase, such an aggregate function exists (called List). In Postgres I can define aggregate functions. I seem stuck in Oracle. Is there anyway to define such aggregate functions in Oracle? Does one exist?

Any help will be greatly appreciated!! (This function is critical in implementing an extension of SQL that is being developed.)

Thanks!
Sara

 

[MikeJones]

Hmm a toughee (well for me anyway) The best I can think of is a function that you would use as

select a, CAT(a) as res
from R


And return :

a | res
=======
1 | 'anna jim'
2 | 'john mary jane'

The function coulds then be written in PLSQL and read all values for a and concatonate them togher but this isn't what you really want. If it's good enough I think Carp or someone posted a function to do it about Hats????

HTH,

Mike.

 

[MikeJones]

I've thought a little more on this and I don't think the way you want it to work is possible using the group by as Oracle doesn't allow you to define functions that can work with it (as far as I now)

Futhermore if you go the path I suggested then you will have a fair amount of dynamic SQL and maybe have to overload it for numbers and charecters. You may also have to pass in the table name unless you know that no column name appears in more than 1 table.