The LISTAGG analytic function was not covered in Roopesh Ramklass book, but it is part of the exam. It makes it very easy to aggregate strings. The nice thing about this function is it also allows us to order the elements in the concatenated list. In the example below, the concatenated list was ordered by the same column as being concatenated, therefore we see employee’s names sorted within the department.

But it could have been sorted by any other column name, for instance by hire_date. In that case, we would get another result, see below

To make it more clear, let’s consider another example :

We can now expand back our LISTAGG function to make sure that 07-DEC-02 is hire_date of Raphaely

Conclusion: as we can see the LISTAGG function aggregates the column values into one row.

Leave a comment

Your email address will not be published. Required fields are marked *