Re: Column aggregating for text columns

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 Sat May 25 2002 - 01:52:42 CEST