Re: Column aggregating for text columns
Date: 23 May 2002 12:01:07 -0700
> >I found this interesting question on another forum. I am going to work on
> >it, but if anyone has a solution please post it.
Sorry folks, I started the thread and vanished. Sorry Joe (Celko), for not posting the DDL (I know, you hate it!! ).
OK, here is my take on solving the problem. This runs only on DB2 though.
I created a table called Test, here is the DDL for the table:
CREATE TABLE "DB2ADMIN"."TEST" (
"ID" INTEGER NOT NULL ,
"NAME" CHAR(10) NOT NULL )
IN "USERSPACE1" ; ID and Name together form the PK. (DDL omitted)
Later I inserted the following rows to function as the test data:
insert into test values (1, 'A') insert into test values (1, 'B') insert into test values (1, 'C') insert into test values (2, 'P') insert into test values (2, 'Q') insert into test values (3, 'X') insert into test values (3, 'Y') insert into test values (3, 'Z')
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)
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
The query returns the following results:
Please comment. Received on Thu May 23 2002 - 21:01:07 CEST