Alessandro Lacava

on Designing and Developing Software. In love with Functional Programming.

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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.

Comments