Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: How to "concat" row values into a single column?

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

From: Lou Fangxin <anysql_at_gmail.com>
Date: Tue, 8 Aug 2006 23:25:35 +0800
Message-ID: <ea5907d30608080825r11b1893apba8f3911e99d1a60@mail.gmail.com>


if running on 9i or above, PIPELINE function can be used to do this.

On 7/25/06, Saibabu Devabhaktuni <saibabu_d_at_yahoo.com> wrote:
>
> 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?
>
> * From: "Mark W. Farnham" <mwf_at_xxxxxxxx>
> * To: <Rich.Jesse_at_xxxxxx>,
> <oracle-l_at_xxxxxxxxxxxxx>
> * Date: Mon, 24 Jul 2006 16:58:42 -0400
>
> 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 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_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-l
>
>
> * 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
>
>
>

-- 
Welcome!
Home: http://www.IamDBA.com

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Aug 08 2006 - 10:25:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US