Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Column aggregating for text columns

Re: Column aggregating for text columns

From: Lennart Jonsson <lennart_at_kommunicera.umea.se>
Date: 24 May 2002 16:52:42 -0700
Message-ID: <6dae7e65.0205241552.7a52e607@posting.google.com>


svenigalla_at_netsetgo.com (Srini Venigalla) wrote in message news:<22597f96.0205231101.41457edc_at_posting.google.com>...
> > >I found this interesting question on another forum. I am going to work on
> > >it, but if anyone has a solution please post it.
> > >

[...]
> Here is the query. It uses three common table expressions, to derive
> the required results. The final table is the recursive query.
>
> ------------------------
> with t1 (i, id, name) as
> (select row_number () over (), id, name from test),
> t2(i, j, id) as (select min (i), max(i), min (id)from t1 group by id),
> t3 (i, id, name) as
> (select i, id, varchar(name,30) from t1 where (i,id) in (select i,id
> from t2)
> union all
> select c.i, c.id, Rtrim(p.name) ||','|| c.name from t1 c, t3 p where
> c.i = p.i+1 and c.id=p.id
> )
> select id, name from t3 where i in (select j from t2) order by i
>

Quite clever way to solve it. However, if one could define aggregate functions the solution would have been slightly less complicated :-) I'm thinking something like:

select id, my_concat(name) from test group by id

I raised the question some time ago, and Kieran mentioned that Informix and Postgres have an CREATE AGGREGATE command for this. There is also a discussion about how to calculate PRODUCT (which also is fairly complicated to express without an aggregate function) in there. If you use google you can find the thread by searching for lennart_at_kommunicera.umea.se and "user defined column functions"

/Lennart Received on Fri May 24 2002 - 18:52:42 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US