Pictures of chickens, flowers, wheat, a boy looking through a magnifying glass, irrigation pipe, soybean pods, and fruits and vegetables.		Cooperative Extension Service
Cooperative Extension Service		Agricultural Experiment Station
Steve Rea's Oracle Tips, Tricks, and Scripts		Search | Publications | Jobs | Personnel Directory | Links

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;

This Page was Last Updated on 06/04/07

You Are Visitor Number

© 2006 University of Arkansas Division of Agriculture All rights reserved. Last Date Modified 02/09/2008 Webmaster
	University of Arkansas • Division of Agriculture Cooperative Extension Service 2301 South University Avenue Little Rock, Arkansas 72204 • USA Phone (501) 671-2000 • Fax (501) 671-2209	Mission • Disclaimer • EEO • Privacy • FOI