0

Having three tables as follows:

Client (Client_Id)
Order (Order_Id, Client_Id)
OrderItem (OrderItem_Id, Order_Id)

how do I go about getting a list of the ClientId's along with a comma separated list of all OrderItem_Id's (serialized into a string) made made by the client?

Note Clients can't have multiple orders.

Here's an example:

Inputs:

Client: 100
Order: 50, 100
OrderItem: [{1, 50}, {2, 50}, {3, 50}]

Expected result: 100, "1,2,3"

I'm using Oracle 10G (soon to be moved to 11G), no java, no cursors, no temp tables please.

user1514042
  • 1,899
  • 7
  • 31
  • 57

1 Answers1

1

WM_CONCAT if < 11g or LIST_AGG or possibly XMLAGG

Select client_ID, List_Agg(OrderItem_ID, ', ')
from ORDER O
INNER JOIN ORDERITEM OI on OI.Order_ID = O.Order_ID

OR

Select client_ID, wm_concat(OrderItem_ID)
from ORDER O
INNER JOIN ORDERITEM OI on OI.Order_ID = O.Order_ID

Note: WM_CONCAT is an undocumented function, use at your own risk; albeit extremely useful.

Community
  • 1
  • 1
xQbert
  • 34,733
  • 2
  • 41
  • 62
  • are they mutually exclusive - can I use WM_CONCAT with oracle 11G? – user1514042 Feb 04 '13 at 23:46
  • WM_Concat works in 11g; again its undocumented/unsupportted so LIST_AGG would be prefered in an 11g envt; however I understand the need to code in 10g w/ ease of portability to 11g. just know that if you use wm_concat, and it breaks at some point; oracle is going to tell you to use list_Agg. While a bit more painful to use XMLAgg may be best given your current DB version dilemma. – xQbert Feb 04 '13 at 23:51