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: