WM_CONCAT

This function is owned by WMSYS. If  all the column are same except one column. Instaed of displaying so many row u want display the column concatenate with comma and display in single row you can use this.

ex: If one employee works in more than one deptement so you want display all information about employee and his woring departments in a single line in concatenate all department in single line use this WM_CONCAT.

select empno, ename, sal, job, wm_concat(a.deptno) from emp e, dept d
where e.deptno = d.deptno
group by empno, ename, sal, job

out put:
3256, ravi, 2000, (10,20,30)

WM_CONCAT(p1 IN VARCHAR2) RETURN VARCHAR2


CREATE TABLE t (
col1 VARCHAR2(5),
col2 VARCHAR2(20));


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

SELECT * FROM t;
col concat format a40
SELECT col1, wmsys.wm_concat(col2) CONCAT
FROM t
GROUP BY col1;

SELECT col1, TRANSLATE(wmsys.wm_concat(col2), 'A,', 'A ') CONCAT
FROM t
GROUP BY col1;