Hi All,

I have a table which has values say
del_id pt_phase
1 red
1 black
2 red
2 black

I need like
del_id pt_phase
1 red,black
2 red,black

I have written a stored procedure to concatenate the pt_phase.
But this will work fine if i have the unique del_id.ie I have to traverse through the records of "select distinct(del_id) from table name" each time and insert values.
In the below code I dont know how to get ditinct(del_id) and work with it.
If i harcode value eg"1" insead of del_id it is working fine
Please help!!

CREATE PROCEDURE CONCAT1()
LANGUAGE SQL
BEGIN ATOMIC
DECLARE s_phase VARCHAR(1000);

SET s_phase = '';

FOR v as cur1 CURSOR FOR
(
SELECT
ADMINISTRATOR.TABLE_TRUNC_PRODQUALITY.PQ_TASK_PHAS E_NAME AS PQ_TASK_PHASE_NAME

FROM
ADMINISTRATOR.TABLE_TRUNC_PRODQUALITY
WHERE
ADMINISTRATOR.TABLE_TRUNC_PRODQUALITY.del_id = del_id
ORDER BY
ADMINISTRATOR.TABLE_TRUNC_PRODQUALITY.PQ_TASK_PHAS E_NAME
)
DO
IF s_phase = '' THEN
SET s_phase = v.PQ_TASK_PHASE_NAME;
ELSE
SET s_phase = s_phase || ', ' || v.PQ_TASK_PHASE_NAME;
END IF;
END FOR;
INSERT INTO test_prodquality VALUES (v.del_id,s_phase) ;
END
@

This type of question gets asked a lot. Try using recursion. If you need help, search the forum.

Andy