How to combine multiple rows into one rows in SQL

Question

I am using PL/SQL, and I have a very simple query:

select
t.dispatch_date,
t.route,
t.employee_name
from 
dispatch t
where
t.dispatch_date=trunc(sysdate)

However, the result will give me some data like below, because there can be multiple people are assigned in same route: (But I want to combine the name together with ||)

Is there any function I can use to achieve my goal?

Thanks

I'm not one for hyperbole, but there is literally a billion posts on SO about converting multiple rows to one row across all SQL's. — dfundako, Jul 19 '18 at 18:46
Just use `LISTAGG`! This question is kind of the definition of LISTAGG. — The Impaler, Jul 19 '18 at 19:22

score 1 · Accepted Answer · answered Jul 19 '18 at 20:01

1

You are looking for listagg():

select t.dispatch_date, t.route,
       listagg(t.employee_name, '||') within group (order by t.employee_name) as employee_names
from dispatch t
where t.dispatch_date = trunc(sysdate);

answered Jul 19 '18 at 20:01

Gordon Linoff

1,198,228
53
572
709

How to combine multiple rows into one rows in SQL

1 Answers1