grouping on character fields

Database
Enthusiast

grouping on character fields

Hi,
A table tab_1 is defined with 2 columns: group_name and email_ids.Sample data in tab_1 is as follows:

group_name email_ids
---------------- --------------
group1 abc@xyz.com
group1 def@xyz.com
group2 ghi@xyz.com
group2 klm@xyz.com
group2 mno@xyz.com

I am inserting the data into another table tab_2.The requirement is that all the email_ids belonging to a particular group should be in a single row in comma appended format as in:

group_name email_ids
---------------- --------------
group1 abc@xyz.com,def@xyz.com
group2 ghi@xyz.com,klm@xyz.com,mno@xyz.com

Any help in this regard will be appreciated.

Thanks,
Kishore
Tags (1)
1 REPLY
Enthusiast

Re: grouping on character fields

Here is one of the approach kishore,

create table Test_Db.Table1
(
groups VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,
emails VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC);

create table Test_Db.Table2
(
groups VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,
emails VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,
val integer)

insert into Test_Db.Table2
sel
groups,
emails,
csum(1,1) from Test_Db.Table1 group by 1

replace recursive view Test_Db.Table_rec
(groups,emails,val)
as locking row for access
(SELECT groups,emails (VARCHAR (5000)),val
FROM Test_Db.Table2
where val=1
UNION ALL
SELECT a.groups,TRIM(a.emails)||';'||TRIM(b.emails),a.val
FROM Test_Db.Table2 A
JOIN Table_rec B
ON A.groups = B.groups
AND A.val = B.val + 1
);

And Finally
To get your result
sel groups,emails from Test_Db.Table_rec
where (groups,val) in (sel groups,max(val) from Test_Db.Table_rec group by 1);

-Karthik N