There could be a better solution
Code:
SQL> SELECT * FROM cycles;
ID CYCLE_TYPE
---------- ----------
1 10
1 20
1 30
1 40
2 10
2 20
6 rows selected.
SQL> SELECT Id, fn_cycletypes(Id) Types FROM cycles
2 GROUP BY Id;
ID TYPES
---------- -------------------------------------------------------
1 10 20 30 40
2 10 20
SQL> SELECT DISTINCT Id, fn_cycletypes(Id) Types FROM cycles;
ID TYPES
---------- -------------------------------------------------------
1 10 20 30 40
2 10 20
SQL>
-- Function code
CREATE OR REPLACE FUNCTION fn_cycletypes (nid IN NUMBER)
RETURN VARCHAR2
IS
sstr VARCHAR2 (4000) DEFAULT 'X';
BEGIN
FOR cur_row IN (SELECT cycle_type
FROM cycles
WHERE id = nid)
LOOP
IF sstr = 'X'
THEN
sstr := cur_row.cycle_type;
ELSE
sstr := sstr || ' ' || cur_row.cycle_type;
END IF;
END LOOP;
RETURN sstr;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
RETURN sstr;
END fn_cycletypes;
/
HTH
Sameer