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)
START WITH num_of_rows = 1
CONNECT BY PRIOR x.num_of_rows = (x.num_of_rows - 1);
The previuos query selects all the telephone numbers for the customer with cd_customer = 50 and put them in a single comma-separated row
