Demostrate how to concatenate a column value based on another column(s) using wmsys.wm_concat

SQL> desc wmsys.wm_concat
FUNCTION wmsys.wm_concat RETURNS CLOB
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P1                             VARCHAR2                IN


SQL> create table t1 (id number, title varchar2(20));

Table created.

SQL> insert into t1 values(1,'Consultant');

1 row created.

SQL> insert into t1 values (1,'Project Manager');

1 row created.

SQL> insert into t1 values (2,'DBA');

1 row created.

SQL> commit;

Commit complete.


SQL> col concat_title for a60
SQL> select id,                           
  2         wmsys.wm_concat(title) concat_title
  3  from t1
  4  group by id;

        ID CONCAT_TITLE
---------- ------------------------------------------------------------
         1 Consultant,Project Manager
         2 DBA

SQL> select comp_name, version, schema from dba_registry;

COMP_NAME                                     VERSION     SCHEMA
--------------------------------------------- ----------- ---------------
OWB                                           11.2.0.2.0  OWBSYS
Oracle Application Express                    3.2.1.00.12 APEX_030200
Oracle Enterprise Manager                     11.2.0.2.0  SYSMAN
Spatial                                       11.2.0.2.0  MDSYS
OLAP Catalog                                  11.2.0.2.0  OLAPSYS
Oracle OLAP API                               11.2.0.2.0  SYS
OLAP Analytic Workspace                       11.2.0.2.0  SYS
Oracle Multimedia                             11.2.0.2.0  ORDSYS
Oracle Rules Manager                          11.2.0.2.0  EXFSYS
Oracle XML Database                           11.2.0.2.0  XDB
Oracle Text                                   11.2.0.2.0  CTXSYS
Oracle Expression Filter                      11.2.0.2.0  EXFSYS
Oracle Database Java Packages                 11.2.0.2.0  SYS
Oracle XDK                                    11.2.0.2.0  SYS
JServer JAVA Virtual Machine                  11.2.0.2.0  SYS
Oracle Workspace Manager                      11.2.0.2.0  WMSYS
Oracle Database Packages and Types            11.2.0.2.0  SYS
Oracle Database Catalog Views                 11.2.0.2.0  SYS

18 rows selected.