List multiple row into one column - Oracle

From previous post: http://hcas-knowledgebase.blogspot.my/2012/09/multiple-of-rows-into-single-row.html

There are a few ways depending on what version you have

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

Desired Output:

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

This article is based on a thread from asktom.oracle.com and contains several methods to achieve the desired results.

• LISTAGG Analytic Function in 11g Release 2
• WM_CONCAT Built-in Function (Not Supported)
• User-Defined Aggregate Function
• Specific Function
• Generic Function using Ref Cursor
• ROW_NUMBER() and SYS_CONNECT_BY_PATH functions in Oracle 9i
• COLLECT function in Oracle 10g

 Source: https://oracle-base.com/articles/misc/string-aggregation-techniques


Remove the duplicates after concatenation
You can use regular expressions and regexp_replace to remove the duplicates after concatenation with listagg:

SELECT Num1, 
       RTRIM(
         REGEXP_REPLACE(
           (listagg(Num2,'-') WITHIN GROUP (ORDER BY Num2) OVER ()), 
           '([^-]*)(-\1)+($|-)', 
           '\1\3'),
         '-') Num2s 
FROM ListAggTest;