RE: How to "concat" row values into a single column?

Thanks, Igor, but the CONNECT function won't get here until we can upgrade to 10g.

The good news is that when I Googled "oracle 10g collect function" to verify that, this page came up:

http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php

The other good news is that Tom Kyte's STRAGG function, as mentioned by Charles and Michael is just what I'm looking for.

Thanks all for your help! I wasn't having any luck perusing the docs, Google, and MetaLink with all the generic search words I was using.

Rich

-----Original Message-----
From: Igor Neyman [mailto:ineyman_at_perceptron.com] Sent: Monday, July 24, 2006 2:10 PM
To: Jesse, Rich; oracle-l_at_freelists.org
Subject: RE: How to "concat" row values into a single column?

CREATE or replace TYPE address_list AS TABLE OF varchar2(2000);
/

SELECT AT.AT_ID, AT.ALARM_INTERVAL, CAST(COLLECT(C.EMAIL_ADDRESS) AS address_list)

   FROM alarm_type AT, contact C
   WHERE AT.AT_ID = 1
   group by AT.AT_ID, AT.ALARM_INTERVAL
/

Now, just process "address_list" string to get rid of "extra" stuff there ("ADDRESS_LIST(" - in the beginning, and ")" - in the end).

Igor

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jesse, Rich Sent: Monday, July 24, 2006 2:34 PM
To: oracle-l_at_freelists.org
Subject: How to "concat" row values into a single column?

Arg. I know I've seen this before and I may have actually done it before, but I'll be darned if I can remember how.

In 9.2, I have two tables kinda like these:

[snip]

--
http://www.freelists.org/webpage/oracle-l