Aggregate multiple rows into one as a comma-delimited list

If you worked with SQL for a while, you'll probably have had the desire to combine multiple rows into a single one to get a better overview. That's something lots of people have done using the language from which they ran their SQL queries. Luckily, Oracle provides a number of solutions to this.

Recipe #1 - Aggregate multiple rows with LISTAGG

If you look at a list of employees by department, it's hard to see how many people are in a department. You'd have to check every line to do that. But let's have a look at the following statement:

SELECT deptno,
       LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) employees,
       COUNT(*) number_of_employees
FROM emp
GROUP BY deptno;

The aggregate function LISTAGG is only available in 11g or later. If you're using an older version of Oracle, have a look at WM_CONCAT — it's an undocumented function, but it works well and is probably used in more places than it should.