Tuesday, February 22, 2011

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.