listagg

A few days back on Twitter, a cool little discussion arose out of the SQL Daily regular tip – the lack of an aggregate function that will return a aggregated product of a set of numbers.

I was doing some code review today of a PL/SQL package, and came across a routine to generate a list of employees for a department.  Truth be told, it wasn’t employees and departments but some anonymity is called for here.

It’s a simple requirement. We want to transform this:

SQL> select deptno, ename
  2  from   emp
  3  order by 1,2;

    DEPTNO ENAME
---------- ----------
        10 CLARK
        10 KING
        10 MILLER
        20 ADAMS
        20 FORD
        20 JONES
        20 SCOTT
        20 SMITH
        30 ALLEN
        30 BLAKE
        30 JAMES
        30 MARTIN
        30 TURNER
        30 WARD

into this: