RE: How to "concat" row values into a single column?
- From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
- To: <Rich.Jesse@xxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 24 Jul 2006 15:10:25 -0400
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:
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@xxxxxxxxxx');
Insert into CONTACT
(CONTACT_ID, AT_ID, EMAIL_ADDRESS)
Values
(4, 1, 'else@xxxxxxxxxx');
COMMIT;
I would like a query to return a single row:
1 10 someone@xxxxxxxxxx, else@xxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
