Some times we need to Place data which are in multiple row, into a single column. suppose we have a table EMP which contains following data
SQL> select JOB,ENAME from emp order by job;
JOB ENAME
--------- ----------
ANALYST SCOTT
ANALYST FORD
CLERK MILLER
CLERK JAMES
CLERK SMITH
CLERK ADAMS
MANAGER BLAKE
MANAGER JONES
MANAGER CLARK
PRESIDENT KING
SALESMAN TURNER
SALESMAN MARTIN
SALESMAN WARD
SALESMAN ALLEN
14 row selected
But we need the out put of the sql quarry in following format:
JOB ENAME
--------- ----------
ANALYST SCOTT, FORD
CLERK MILLER, JAMES, SMITH, ADAMS
MANAGER BLAKE, JONES, CLARK
PRESIDENT KING
SALESMAN TURNER, MARTIN, WARD, ALLEN
To obtain such formated output we should rewrite the sql quarry as follows:
SQL> select JOB, REPLACE( REPLACE( XMLAGG
(XMLELEMENT("#",ENAME)).GETSTRINGVAL(),'<#>',' '),'',', ' )"ENAME" from emp group by job order by job;
JOB ENAME
--- ----------------------------------
ANALYST SCOTT, FORD,
CLERK SMITH, JAMES, ADAMS, MILLER,
MANAGER JONES, CLARK, BLAKE,
PRESIDENT KING,
SALESMAN ALLEN, WARD, TURNER, MARTIN,
No comments:
Post a Comment