Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to "concat" row values into a single column?
How about something like this:
create table test1 as select * from dba_objects;
create or replace function test_f (vid in varchar2)
return varchar2
as
vout varchar2(50);
begin
for i in (select object_id from test1 where
object_name=vid) loop
vout := vout||' '||i.object_id;
end loop;
return vout;
end;
/
select distinct object_name, test_f(object_name) from test1 where object_name='TEST_OBJ';
OBJECT_NAME NAME ------------------------------ ---------------------------------------- TEST_OBJ 2101 24011 24075 241258
You can use similar functionality in your case.
Thanks,
Sai.
http://sai-oracle.blogspot.com
RE: How to "concat" row values into a single column?
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_xxxxxxxxxxxxx
[mailto:oracle-l-bounce_at_xxxxxxxxxxxxx]On
Behalf Of Jesse, Rich
Sent: Monday, July 24, 2006 2:34 PM
To: oracle-l_at_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 NOTNULL
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) NOTNULL
Insert into CONTACT
(CONTACT_ID, AT_ID, EMAIL_ADDRESS)
Values
(3, 1, 'someone_at_xxxxxxxxxx');
Insert into CONTACT
(CONTACT_ID, AT_ID, EMAIL_ADDRESS)
Values
(4, 1, 'else_at_xxxxxxxxxx');
COMMIT;
I would like a query to return a single row:
1 10 someone_at_xxxxxxxxxx, else_at_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
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Mon Jul 24 2006 - 19:13:56 CDT
* References:
o How to "concat" row values into a single column? + From: Jesse, Rich __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- http://www.freelists.org/webpage/oracle-l
![]() |
![]() |