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:
SQL:
-
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
