I have one table on AS400 DB2 database something like with three columns...

*FirstName LastName Department*

James Anderson Computer Science
James Anderson Information Technology
James Anderson Information System
Robert McCafee Heath Care
Robert McCafee Biology
Suman Kapoor Compter Science
Suman Kapoor Information Technology
Luke Morety Computer Science
....
....
....



Final table I am looking is look something like.....

*FirstName LastName Department*

James Anderson Computer Science, Information Technology, Information System
Robert McCafee Health Care, Bilology
Suman Kapoor Computer Science, Information Technology
Luke Morety Computer Science
......
......
......


Any idea with AS400 DB2 syntax pls? thanks....

I don't think SQL is going to be they best way to do that. IF you can get it to work you will need a select distinct to get the individual names, then self-join this back to the table to get the departments. But since this is going to be a variable number of rows I'm not sure exactly how to to do that.

It would probably be quicker to write a program.

Here's an example using a user-defined function.
select name, dept_list(name) FROM(select name from dept_table group by name) as
namelist;


create function dept_list (iname varchar(10))
returns varchar(1000)
language sql
begin
declare templist varchar(1000) default '';

FOR v1 AS clist CURSOR FOR select dept from dept_table where lastname=iname
DO
SET templist = templist || dept || ' ';
END FOR;

RETURN templist;
END;

MarkWagoner wrote: