Sunday, May 11, 2008

Place multiple row in a single column

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: