How to put a set of rows into a single row in Oracle

In Oracle you can execute hierarchical queries using some cool operators–CONNECT BY PRIOR, START WITH and the pseudocolumn SYS_CONNECT_BY_PATH, namely. However, using your fantasy you can exploit these operators to put in a single row–with each item separated by the next through a separator, such as a comma–what you have in multiple rows. For example if you have more than one telephone number in different rows and you want to put them in a single comma-separated row you could use a query similar to the following:


SELECT ltrim(sys_connect_by_path(telephone, ', '), ', ') tel
FROM (
SELECT telephone, rownum num_of_rows
FROM (-- Select the first 9 rows    (1)
SELECT ds_number telephone
FROM telephones t
WHERE cd_customer = 50
AND rownum <= 9)) x
WHERE num_of_rows = (-- Count the # of rows selected in (1)
SELECT COUNT(*)
FROM telephones t
WHERE cd_customer = 50
AND rownum <= 9)
CONNECT BY PRIOR x.num_of_rows = (x.num_of_rows - 1);