CODEWITHSUNDEEP
sqloracle-database
Mark Sherretta
Mark Sherretta

Reputation: 10230

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.

Upvotes: 1

Views: 250

Answers (4)

Primal Suaris
Primal Suaris

Reputation: 1

In oracle you can use wm_concat() 

SELECT a.Name , wm_concat(b.Name) FROM 
   ALERT a, 
   ALERT_BRAND_XREF  abx ,
   BRAND b 
  where a.id = abx.ALERT_ID 
   and abx.BRAND_ID = b.id 
  group by  a.Name;

Upvotes: 0

drnk
drnk

Reputation: 764

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

Upvotes: 1

Walter Mitty
Walter Mitty

Reputation: 18940

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

Upvotes: 0

Chad Birch
Chad Birch

Reputation: 74528

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)

Upvotes: 1

Related Questions