the collect function in 10g

Oracle 10g has introduced an extremely useful new group function, COLLECT. This function enables us to aggregate data into a collection, retaining multiple records of data within a single row (like a nested table). One of the main benefits of this function is that it makes "string aggregation" (one of the web's most-requested Oracle technique) very simple

SQL> SELECT deptno   2  ,      COLLECT(ename) AS emps   3  FROM   emp   4  GROUP  BY   5         deptno;  

    DEPTNO EMPS ---------- ------------------------------------------------------------------------------------         10 SYSTPXeCjDqbWSqWrshgYrRPR4Q==('CLARK', 'KING')         20 SYSTPXeCjDqbWSqWrshgYrRPR4Q==('SMITH', 'JONES', 'SCOTT', 'ADAMS', 'FORD')         30 SYSTPXeCjDqbWSqWrshgYrRPR4Q==('ALLEN', 'WARD', 'MARTIN', 'BLAKE', 'TURNER', 'JAMES')         40 SYSTPXeCjDqbWSqWrshgYrRPR4Q==('MILLER')  4 rows selected.