The fuction of listagg.

LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values of the <measure> column.

• As a single-set aggregate function, LISTAGG operates on all rows and returns a single output row.

• As a group-set aggregate, the function operates on and returns an output row for each group defined by the GROUP BY clause.

• As an analytic function, LISTAGG partitions the query result set into groups based on one or more expression in the query_partition_clause .

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> ed
已写入 file afiedt.buf

  1  select deptno, listagg(job,';') within group (order by job) jobs
  2    from  scott.emp
  3    group by deptno
  4*  order by deptno
SQL> /

    DEPTNO
----------
JOBS
--------------------------------------------------------------------------------
        10
CLERK;MANAGER;PRESIDENT

        20
ANALYST;ANALYST;CLERK;CLERK;MANAGER

        30
CLERK;MANAGER;SALESMAN;SALESMAN;SALESMAN;SALESMAN

We can use the distinct in the sub query to remove the duplicated records.
SQL> ed
已写入 file afiedt.buf

  1  select deptno, listagg(job,';') within group (order by job) jobs
  2     from (select distinct deptno, job from scott.emp)
  3     group by deptno
  4*   order by deptno
SQL> /

    DEPTNO
----------
JOBS
--------------------------------------------------------------------------------
        10
CLERK;MANAGER;PRESIDENT

        20
ANALYST;CLERK;MANAGER

        30
CLERK;MANAGER;SALESMAN