Hi,

I have a table in DB2 UDB with data that looks like this:
col1 col2 col3
1 |500 |15
1 |1500 |12
1 |1000 |10
2 |300 |12
2 |800 |10


I want to create a new table in DB2 UDB that takes this data and displays it as follows:
col1 col2 col3
1 |500,1500,1000 |15,12,10
2 |300,800 |12,10

Does anyone know how I can do this in DB2 UDB?

As this is an old posting, this information is for those who have a similar question in the future.

One way to approach this problem is to create a function(s) which contains a FOR loop which would concatenate the data found for the value of col1 passed to the function. Example:
CREATE FUNCTION testf(id integer)
LANGUAGE SQL
NOT DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
RETURNS VARCHAR(1000)
BEGIN ATOMIC
FOR X AS
SELECT col2 FROM t
WHERE col1 = id
DO
SET v_value = col2 || ', ';
END FOR;
RETURN SUBSTR(v_value, 1, LENGTH(v_value) - 2);
END

To use it:
INSERT INTO test2 (col1, col2, col3)
SELECT col1, testf(col1), testf2(col1) FROM t