Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle WM_CONCAT Function      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

Term: WM_CONCAT

Definition:
The Oracle PL/SQL WM_CONCAT function is used to aggregate data from a number of rows into a single row, giving a list of data associated with a specific value. In effect, it cross-tabulates a comma delimited list.

Note that WM_CONCAT is undocumented and unsupported by Oracle, meaning it should not be used in production systems. The LISTAGG function, which can produce the same output as WM_CONCAT is both documented and supported by Oracle.


Example Usage:

CREATE TABLE test_wm (
column_1 VARCHAR2(5),
column_2 VARCHAR2(20));

INSERT INTO test_wm VALUES (111, 'This');
INSERT INTO test_wm VALUES (111, 'is');
INSERT INTO test_wm VALUES (111, 'a');
INSERT INTO test_wm VALUES (111, 'test');
INSERT INTO test_wm VALUES (222, 'This is not');

SELECT * FROM test_wm;

col concat format a40

SELECT column_1, wmsys.wm_concat(column_2) CONCAT
FROM test_wm
GROUP BY column_1;

SELECT column_1, TRANSLATE(wmsys.wm_concat(column_2), 'A,', 'A ') CONCAT
FROM test_wm
GROUP BY column_1;


Related Links:
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 315 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?