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

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Mon, 24 Jul 2006 16:58:42 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKGEFJIFAA.mwf@rsiz.com>

Do you mean:

select to_char(a.at_id)||' '||to_char(alarm_interval)||' '||c3.email_address||' '||c4.email_address from alarm_type a, contact c3, contact c4 where a.at_id = 1

      and c3.at_id = a.at_id
      and c4.at_id = a.at_id
      and c3.contact_id = 3
      and c4.contact_id = 4;

If you're at a sufficient RDBMS Level, you might profit from

from alarm_type a, (select at_id,email_address from contact where contact_id = 3) c3, (select at_id,email_address from contact where contact_id = 4) c4

and losing the last two ands. (Although in a perfect CBO world it wouldn't matter).

-----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:

CREATE TABLE ALARM_TYPE
(

  AT_ID           NUMBER                        NOT NULL,
  ALARM_INTERVAL  NUMBER                        NOT NULL

)
/

Insert into ALARM_TYPE

(AT_ID, ALARM_INTERVAL)

Values
(1, 10);

COMMIT; CREATE TABLE CONTACT
(

  CONTACT_ID           NUMBER                   NOT NULL,
  AT_ID                NUMBER                   NOT NULL,
  EMAIL_ADDRESS        VARCHAR2(200)            NOT NULL

)
/

Insert into CONTACT

(CONTACT_ID, AT_ID, EMAIL_ADDRESS)

Values
(3, 1, 'someone_at_oracle.com');

Insert into CONTACT

(CONTACT_ID, AT_ID, EMAIL_ADDRESS)

Values
(4, 1, 'else_at_oracle.com');

COMMIT; I would like a query to return a single row:

1 10 someone_at_oracle.com, else_at_oracle.com

I had tried fudging SYS_CONNECT_BY_PATH and some of the XML functions, but I'm not having any luck. Something along those lines would be an ideal solution for me for this project. But since these are new tables, I'm not opposed to a structure change, although I'm not sure I want to go with the OO approach (e.g. VARRAY column for the email addresses).

Of course, this is way-oversimplified for the sake of brevity.

Anyone???

TIA!
Rich

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


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

Received on Mon Jul 24 2006 - 15:58:42 CDT