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

In Oracle you can execute hierarchical queries using some cool operators—namely CONNECT BY PRIOR and START WITH—and the pseudocolumn SYS_CONNECT_BY_PATH. With a little imagination, you can use these features to combine multiple rows into a single row, with the items separated by a delimiter such as a comma. 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)
START WITH num_of_rows = 1
CONNECT BY PRIOR x.num_of_rows = (x.num_of_rows - 1);

The previous query selects all the telephone numbers for the customer with cd_customer = 50 and puts them in a single comma-separated row.