Concatenating Multiple Rows Into A Single String

If you have multiple rows that you want to concatenate into a single string, such as creating a string of e-mail addresses from a list of those addresses returned by a "select" statement, you can use this tip that I found at asktom.oracle.com, which makes a novel use of the Oracle analytical functions.

In the example below, we are getting a list of e-mail addresses for customers with a zip code of 72204, and concatenating that list into a comma-separated string.  To plug in your own SQL, simply replace "select email_addr myvalues from customers where zip = 72204" with your own SQL that returns a single column of values, and label that column "myvalues".

In the example below, "myvalues" is the label of the list of e-mail addresses returned by the "select from customers" SQL.  "row_number() over (order by myvalues)" produces a sequential number for each record returned by the "select from customers" SQL (this column is labeled "rn"), where the records are ordered by the e-mail address.  "count(*) over ()" produces the total number of e-mail addresses returned by the "select from customers" SQL (this column is labeled "cnt").  "ltrim(sys_connect_by_path(myvalues, ','),',')" concatenates the "path" of values created by the "start with" and "connect by prior" chain, placing a comma between the values and, then, removing the leftmost comma (this column is labeled "catvalues").  "rn = cnt" selects the row where the row number equals the count of records, thus just returning the final row with the full path of values.  You can get an idea of what this is doing by removing the "where rn = cnt" line and seeing the records that are returned.

with data
   as
   (
      select myvalues, row_number() over (order by myvalues) rn, count(*) over () cnt
         from
         (
            select email_addr myvalues from customers where zip = 72204
         )
   )
select ltrim(sys_connect_by_path(myvalues, ','),',') catvalues
   from data
   where rn = cnt
   start with rn = 1
   connect by prior rn = rn-1;

Copyright © 2009 by Maristream.   All information, scripts, forms, and other material
on this web site are freely available to all Banner and Oracle Database Administrators,
Systems Administrators, Programmers, and others that may need it.

The webmaster who maintains this web site may be reached at srea@maristream.org. Visit our other web sites:

Disclaimer:  As with all software, especially where it affects your vital data, make sure that you examine theses scripts and that you understand what they do before you use them to see if they would have any adverse effect on your particular setup or database layout.  Make a full backup of your database in case you have to revert to your original copy of the database before the scripts were run.  Use these scripts at your own risk.  As a condition of using these scripts, you agree to hold harmless both Maristream and Stephen Rea for any problems that they may cause or other situations that may arise from their use, and that neither Maristream nor I will be held liable for those consequences.