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

  • To: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 24 Jul 2006 14:23:29 -0500

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@xxxxxxxxxxxxxx] 
Sent: Monday, July 24, 2006 2:10 PM
To: Jesse, Rich; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jesse, Rich
Sent: Monday, July 24, 2006 2:34 PM
To: oracle-l@xxxxxxxxxxxxx
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]
--
//www.freelists.org/webpage/oracle-l


Other related posts: