I had the same problem as you and this is the solution I came up with using just SQL and no PLSQL. But it does require 9i : (.
The query uses the analytic functions to allow it create a linked list of data. The linked list is then converted to a tree using CONNECT BY. And finally it uses SYS_CONNECT_BY_PATH to create your list of values before grouping the data. The query may require tuning of indexes and or hints depending on the volume and distribution of data.
Query to generate a list of values from a child table (in CSV format) for every primary key value in a parent table.
if parent table has
and the child table has
this query will output
id list of value_ids
The only issue I can think of is what happens if the list is longer than the varchar2 limit of 4000 bytes.
Obviously this only works with 9i due to the SYS_CONNECT_BY_PATH : (.
-- get the longest csv list of values for each id
SUBSTR(MAX(list) KEEP (DENSE_RANK FIRST ORDER BY (lev) desc),2) as lov
-- create a tree and use sys_connect_by_path to create a csv list of values
select id , SYS_CONNECT_BY_PATH(value_id, ',') list, level lev
-- create a result set with a linked list column for every id so that we can create a hierarchical tree with the next query
select r.id, i.value_id, rownum prow,
LAG(rownum, 1) OVER (PARTITION BY i.id ORDER BY value_id) as connect_id
from parent_table r, child_table i
where r.id = i.id
START WITH connect_id is null
CONNECT BY PRIOR prow=connect_id
group by id
order by id