Concatenating multiple results of a query in one row in Oracle

Question

I have 2 tables with one having a reference to the first by id

first table for example is customer having the fields

    id       firstname     lastname
    ------   ---------     ---------
    1        john          smith
    2        jessica       james

the second table for example is product having the fields

   id        customer_id     product     descr
   -------   -----------     ---------   ------
   1         1               ts          Shirt
   2         1               ti          Tie
   3         2               sk          skrit

I need a query that will output the following

   customer.firstname  customer.lastname    product_and_desc
   ------------------  ------------------   ---------------------
   john                smith                ts-Shirt , ti-Tie
   jessica             james                sk-skirt

with the product rows variable for each customer.

I appreciate you help :)

thanks,

have you tried this? http://stackoverflow.com/questions/4686543/sql-to-concatenate-column-values-from-multiple-rows — hrezs, May 17 '13 at 00:41
was just trying it and it worked :) not sure if I should keep the question, seems like I didn't do my research well before posting :$ — wassim, May 17 '13 at 01:11

score 1 · Accepted Answer · answered May 17 '13 at 01:28

You can use list_agg(). In your case:

select c.firstname, c.lastname,
       list_agg(p.product||'-'||p.desc, ' , ') within group (order by p.id) as product_and_desc
from customer c join
     product p
     on c.id = p.customer_id
group by c.firstname, c.lastname;

I would suggest, though, that the second argument to list_agg() be ', ' rather than ' , '. The space before the comma looks a bit unusual.

score 1 · Answer 2 · answered May 17 '13 at 06:51

1

select first_name,last_name,wm_concat(product||'-'||descr) as product_and_descr
from tbl1 ,tbl2 where tbl1.id=tbl2.customer_id
group by first_name,last_name;

answered May 17 '13 at 06:51

Aspirant

2,238
9
31
43

Concatenating multiple results of a query in one row in Oracle

2 Answers2