Oracle in-line method to produce CSV for relation

Question

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.

Answer 1

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)

Answer 2

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).

Answer 3

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