Hi Experts,
My Oracle Version:
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for 32-bit Windows: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
Query:
SELECT
DEPT.DEPTNO DEPTNO,
WM_CONCAT(EMPNO) EMPNO,
WM_CONCAT(ENAME) ENAME,
WM_CONCAT(JOB) JOB
FROM
EMP EMP,
DEPT DEPT
WHERE
DEPT.DEPTNO IN (10,20)
GROUP BY DEPT.DEPTNO
Output:
DEPTNO EMPNO ENAME JOB
10 7369,7499,7521,.... SMITH,ALLEN,JONES,MARTIN,..... CLERK,SALESMAN,PRESIDENT,CLERK,....
20 7369,7521,7499,..... SMITH,ALLEN,JONES,MARTIN,..... CLERK,SALESMAN,PRESIDENT,CLERK,....
Query returns multiple Empno,Ename and job with comma separator for each deptno.
But I need to concat the rows again into a single column with separator (say ##).
Required Output:
DEPTNO EMPNO ENAME
10##20 7369,7499,7521,....##7369,7521,7499,..... SMITH,ALLEN,JONES,MARTIN,.....##SMITH,ALLEN,JONES,MARTIN,.....
Thanks,
Dharan V