Re: Column aggregating for text columns

From: Srini Venigalla <svenigalla_at_netsetgo.com>
Date: 23 May 2002 12:01:07 -0700
Message-ID: <22597f96.0205231101.41457edc@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.
> >

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:

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 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)
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

---

The query returns the following results:

id Name
-- ----
1 A,B,C

2 P,Q

3 X,Y,Z

---

Please comment. Received on Thu May 23 2002 - 14:01:07 CDT