CODEWITHSUNDEEP
sqloracle-databaseconcatenation
wassim
wassim

Reputation: 377

Concatenating multiple results of a query in one row in Oracle

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,

Upvotes: 0

Views: 1533

Answers (2)

Aspirant
Aspirant

Reputation: 2278

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;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269483

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.

Upvotes: 1

Related Questions