Oracle in-line method to produce CSV for relation

I have the following tables:

ALERT (ID,Name)
   1  |  Alert A
   2  |  Alert B


ALERT_BRAND_XREF (ALERT_ID, BRAND_ID)
   1  | 1
   1  | 2
   2  | 1

BRAND (ID, NAME)
  1  | Brand A
  2  | Brand B

I am trying to write one statement to return a list of alerts with the applicable brands as a CSV list in one field. Desired results:

 Alert A  |  Brand A, Brand B
 Alert B  |  Brand A

Is there a way to do this without writing a separate function? I would like to do it in one self-contained SQL statement if possible.

This is Oracle 9i.

From stackoverflow

• In MySQL this would be easy with the GROUP_CONCAT() function, but it looks like to do the equivalent in Oracle it's a little messy:

  Oracle group_concat() updated (again)

• Here's another way to turn a repeating group into a comma setarated list. It uses the MODEL clause of Oracle's dialect of SQL. (Oracle 10g)

  http://plsqlnotes.blogspot.com/2007/09/using-model-for-generating-csv-by_2227.html#links

  (Replaces my previous wrong answer).

  Nebakanezer : I wasn't aware of a built-in CSV generator. Any other details? Link?

  Walter Mitty : Oops, my blunder. I didn't read your problem carefully. I thought you were trying to dump the whole thing to a CSV file. Sorry!

• Look to this solutions, its very useful. Using SYS_CONNECT_BY_PATH and analytic functions.