How to combine multiple rows into one line

Question

I have a very simple query:

select date,route,employee
from information
where date=Trunc(Sysdate)

however, since for some routes, there are more than 2 employees are assigned, so the query will return two rows

But I want get one route for one row, so the ideal output should be:

so the two names are in the same row, and combine with "|", so how can I achieve this goal in PL/SQL?

Answer 1

You can use listagg function, but you have to add Date and Route to grouping functions as well

SELECT LISTAGG(emp, ' | ')
     WITHIN GROUP (ORDER BY emp) "Emp",
       MAX(date) "Date",
       MAX(route) "Route"
FROM information
WHERE date=Trunc(Sysdate);