Better String Aggregation since Oracle 11gR2

In my previous blog "String aggregate in Oracle", I mentioned the restriction of that function is no ordering. Actually since Oracle 11g Release 2, Oracle introduced a new function LISTAGG which provides the same functionality, and also with ordering. You can find details here.

Anyway, I'll show some quick examples in this blog too. To achieve the same result, the query below can be used:

    select employee_id,
           LISTAGG(job_id, '|') WITHIN GROUP (ORDER BY job_id) job_list
    from job_history
    group by employee_id;

And below is the result.

    EMPLOYEE_ID JOB_LIST                     
    ----------- --------------------
            101 AC_ACCOUNT|AC_MGR   
            102 IT_PROG             
            114 ST_CLERK            
            122 ST_CLERK            
            176 SA_MAN|SA_REP       
            200 AC_ACCOUNT|AD_ASST
            201 MK_REP             

And you can see every item in job_list is order.

This new function can be used as analytical function as well.