Multiple of rows into a single column

I was wondering how to make this so i googled on web and found this.
There are several methods to achieve the desired results:

1 LISTAGG Analytic Function in 11g Release 2
2 WM_CONCAT Built-in Function
3 Specific Function
4 Generic Function using Ref Cursor
5 User-Defined Aggregate Function
6 ROW_NUMBER() and SYS_CONNECT_BY_PATH functions in Oracle 9i
7 COLLECT function in Oracle 10g

Example Scenario:


Base Data:

DEPTNO ENAME
---------- ----------
20 SMITH
30 ALLEN
30 WARD
20 JONES
30 MARTIN
30 BLAKE
10 CLARK
20 SCOTT
10 KING
30 TURNER
20 ADAMS
30 JAMES
20 FORD
10 MILLER

Desired Output:

DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

If this is scenario & result you want so try apply the method state above.
you can refer this -> String Aggregation Techniques

Other reference for Oracle 9i -> User-defined
Oracle group_concat() -> Tom Kyte

Else if you found solution using something like "XMLAgg"
*this solution proved very resources intensive with requests involving something like 10⁵ rows. I ended up replacing this by custom aggregate functions as suggested by John.

Good luck with that.


-------------------------------------------------------
Update
-------------------------------------------------------
Multiple row in one column using 9i, i test using technique SYS_CONNECT_BY_PATH Operator but not success then i using other technique xmlagg and already tested and its working like charm! 

Oracle 9i xmlagg