Use xmlagg in Oracle to concat multiple rows into String

Or in other words, Pivot the values from the results of a query.

The following article contains a lot of information about this problem that we usually face:
http://tkyte.blogspot.com/2007/03/stringing-them-up.html

Read the comments section.

This is the most intuitive solution that I prefer:

SELECT deptno,
       SUBSTR (REPLACE (REPLACE (XMLAGG (XMLELEMENT ("x", ename)
                                 ORDER BY ename),'</x>'),'<x>','|'),2)  as concated_list
FROM emp
    GROUP BY deptno
    ORDER BY deptno;