Possible recursive query in Oracle (pre-11g) to concatenate nested string values

Question

I have an issue regarding multi-row comments in a table that I need to extract as a single string via SQL. I have two tables: test and comment. Here's some example data for each with the desired result set I'm looking for:

I'm honestly not even sure if what I'm looking for is possible without PL/SQL or some form of external programming language. I've been jousting with connect by for a bit and had no luck. Any help would be greatly appreciated.

EDIT: I should mention that the number of result_key values per comment_key value is variable, and that string format doesn't matter (spacing, etc.). So, in a nutshell, Result_Text(n)+Result_Text(n+1)+...

Answer 1

select 
  id, 
  name, 
  replace(replace(wmsys.wm_concat(replace(result_text, ',', chr(1)), 
    ',', ' '), chr(1), ',') as Comment
from (
  select id, name, result_text 
  from test left join comment using(comment_key)
  order by id, result_key
)
group by id, name
order by id

Answer 2

See SQL Fiddle for a demonstration of the following query:

SELECT Test.ID, Test.Name,
  get_comma_separated_value(Test.ID) AS CommConcat
FROM Test

Oh, based on the information you provided for English, your result would only be English comment..

Here is the declaration of the function get_comma_separated_value:

CREATE OR REPLACE FUNCTION get_comma_separated_value (input_val  in  VARCHAR2)
  RETURN VARCHAR2
IS
  return_text  VARCHAR2(10000) := NULL;
BEGIN
  FOR x IN 
  (
    SELECT Result_Text
    FROM Test
    INNER JOIN Comments ON Test.Comment_Key = Comments.Comment_Key
    WHERE Test.ID = input_val
    ORDER BY Comments.Result_Key 
  ) 
  LOOP
    return_text := return_text || ' ' || x.Result_Text ;
  END LOOP;
  RETURN LTRIM(return_text, ' ');
END;
/