Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 7718 invoked from network); 8 Aug 2006 10:33:04 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 8 Aug 2006 10:32:45 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0E80B3BDDBD; Tue, 8 Aug 2006 11:32:25 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 09235-03; Tue, 8 Aug 2006 11:32:24 -0400 (EDT) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6D5D43BDCAB; Tue, 8 Aug 2006 11:32:24 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 08 Aug 2006 11:31:23 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8198B3BDC84 for ; Tue, 8 Aug 2006 11:31:23 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 09040-09 for ; Tue, 8 Aug 2006 11:31:23 -0400 (EDT) Received: from mail67.messagelabs.com (mail67.messagelabs.com [193.109.254.83]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id 961A03BDC44 for ; Tue, 8 Aug 2006 11:31:22 -0400 (EDT) X-VirusChecked: Checked X-Env-Sender: Anthony.Ashton@dti.gsi.gov.uk X-Msg-Ref: server-12.tower-67.messagelabs.com!1155051096!14572703!1 X-StarScan-Version: 5.5.10.7; banners=dti.gsi.gov.uk,-,- X-Originating-IP: [62.25.106.208] Received: (qmail 1060 invoked from network); 8 Aug 2006 15:31:36 -0000 Received: from gateway-102.energis.gsi.gov.uk (HELO mx.hosting-w.gsi.gov.uk) (62.25.106.208) by server-12.tower-67.messagelabs.com with SMTP; 8 Aug 2006 15:31:36 -0000 X-IPRELAY: ndcirn01.dti.local X-IronPort-AV: i="4.07,222,1151881200"; d="scan'208,217"; a="12509979:sNHT53193945" Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C6BAFF.BC10E409" X-MimeOLE: Produced By Microsoft Exchange V6.5 Subject: RE: How to "concat" row values into a single column? Date: Tue, 8 Aug 2006 16:31:36 +0100 Message-ID: <972D83C792B76A47B176328380D8F6D415EE59@SDCPRV01.dti.local> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: How to "concat" row values into a single column? From: "Ashton Anthony \(Mr A\) ERDU" To: , Cc: X-OriginalArrivalTime: 08 Aug 2006 15:31:36.0584 (UTC) FILETIME=[BC565880:01C6BAFF] X-archive-position: 37884 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: Anthony.Ashton@dti.gsi.gov.uk Precedence: normal Reply-to: Anthony.Ashton@dti.gsi.gov.uk X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at avenirtech.net ------_=_NextPart_001_01C6BAFF.BC10E409 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Search=20Asktom=20for=20STRAGG.=20A=20fantastic=20user=20defined=20aggrega= te=20function,=20well=20worth=20a=20public=20execute =20 Anthony -----Original=20Message----- From:=20oracle-l-bounce@freelists.org=20[mailto:oracle-l-bounce@freelists.= org]On=20Behalf=20Of=20Lou=20Fangxin Sent:=2008=20August=202006=2016:26 To:=20saibabu_d@yahoo.com Cc:=20oracle-l@freelists.org Subject:=20Re:=20How=20to=20"concat"=20row=20values=20into=20a=20single=20= column? if=20running=20on=209i=20or=20above,=20PIPELINE=20function=20can=20be=20us= ed=20to=20do=20this. On=207/25/06,=20Saibabu=20Devabhaktuni=20<=20saibabu_d@yahoo.com>=20wrote:= =20 How=20about=20something=20like=20this: create=20table=20test1=20as=20select=20*=20from=20dba_objects; create=20or=20replace=20function=20test_f=20(vid=20in=20varchar2)=20 return=20varchar2 as vout=20varchar2(50); begin for=20i=20in=20(select=20object_id=20from=20test1=20where object_name=3Dvid)=20loop vout=20:=3D=20vout||'=20'||i.object_id; end=20loop; return=20vout; end; / select=20distinct=20object_name,=20test_f(object_name)=20 from=20test1=20where=20object_name=3D'TEST_OBJ'; OBJECT_NAME=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20NAM= E ------------------------------ ---------------------------------------- TEST_OBJ=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =202101=2024011=2024075=20241258 You=20can=20use=20similar=20functionality=20in=20your=20case. Thanks, Sai. http://sai-oracle.blogspot.com RE:=20How=20to=20"concat"=20row=20values=20into=20a=20single=20column?=20 =20=20=20*=20From:=20"Mark=20W.=20Farnham"=20 =20=20=20*=20To:=20, =20=20=20*=20Date:=20Mon,=2024=20Jul=202006=2016:58:42=20-0400 Do=20you=20mean: select=20to_char(=20a.at_id)||' '||to_char(alarm_interval)||' '||c3.email_address||'=20'||c4.email_address from=20alarm_type=20a,=20contact=20c3,=20contact=20c4 where=20a.at_id=20=3D=201 =20=20=20=20=20and=20c3.at_id=20=3D=20a.at_id =20=20=20=20=20and=20c4.at_id=20=3D=20a.at_id =20=20=20=20=20and=20c3.contact_id=20=3D=203 =20=20=20=20=20and=20c4.contact_id=20=3D=204; ? If=20you're=20at=20a=20sufficient=20RDBMS=20Level,=20you=20might profit=20from from=20alarm_type=20a,=20(select=20at_id,email_address=20from contact=20where=20contact_id=20 =3D=203)=20c3,=20(select=20at_id,email_address=20from=20contact where=20contact_id=20=3D=204)=20c4 and=20losing=20the=20last=20two=20ands.=20(Although=20in=20a=20perfect CBO=20world=20it=20wouldn't matter). -----Original=20Message----- From:=20oracle-l-bounce@xxxxxxxxxxxxx [mailto:=20oracle-l-bounce@xxxxxxxxxxxxx]On Behalf=20Of=20Jesse,=20Rich Sent:=20Monday,=20July=2024,=202006=202:34=20PM To:=20oracle-l@xxxxxxxxxxxxx Subject:=20How=20to=20"concat"=20row=20values=20into=20a=20single column? Arg.=20=20I=20know=20I've=20seen=20this=20before=20and=20I=20may=20have actually=20done=20it before,=20but=20I'll=20be=20darned=20if=20I=20can=20remember=20how. In=209.2,=20I=20have=20two=20tables=20kinda=20like=20these:=20 CREATE=20TABLE=20ALARM_TYPE ( AT_ID=20=20=20=20=20=20=20=20=20=20=20NUMBER=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20NOT NULL, ALARM_INTERVAL=20=20NUMBER=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20NOT NULL ) / Insert=20into=20ALARM_TYPE =20=20(AT_ID,=20ALARM_INTERVAL)=20 Values =20=20(1,=2010); COMMIT; CREATE=20TABLE=20CONTACT ( CONTACT_ID=20=20=20=20=20=20=20=20=20=20=20NUMBER=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20NOT NULL, AT_ID=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20NUMBER=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20NOT NULL, EMAIL_ADDRESS=20=20=20=20=20=20=20=20VARCHAR2(200)=20=20=20=20=20=20=20=20= =20=20=20=20NOT=20 NULL ) / Insert=20into=20CONTACT =20=20(CONTACT_ID,=20AT_ID,=20EMAIL_ADDRESS) Values =20=20(3,=201,=20'someone@xxxxxxxxxx'); Insert=20into=20CONTACT =20=20(CONTACT_ID,=20AT_ID,=20EMAIL_ADDRESS) Values =20=20(4,=201,=20'else@xxxxxxxxxx=20'); COMMIT; I=20would=20like=20a=20query=20to=20return=20a=20single=20row: 1=20=20=20=20=20=20=2010=20=20=20=20=20=20someone@xxxxxxxxxx,=20else@xxxxx= xxxxx I=20had=20tried=20fudging=20SYS_CONNECT_BY_PATH=20and=20some=20of the=20XML=20functions, but=20I'm=20not=20having=20any=20luck.=20=20Something=20along=20those=20 lines=20would=20be=20an ideal=20solution=20for=20me=20for=20this=20project.=20=20But=20since these=20are=20new=20tables, I'm=20not=20opposed=20to=20a=20structure=20change,=20although=20I'm not=20sure=20I=20want=20to go=20with=20the=20OO=20approach=20(e.g.=20VARRAY=20column=20for=20the=20 email=20addresses). Of=20course,=20this=20is=20way-oversimplified=20for=20the=20sake=20of brevity. Anyone??? TIA! Rich -- http://www.freelists.org/webpage/oracle-l=20=20=20 -- http://www.freelists.org/webpage/oracle-l =20=20=20*=20References: =20=20=20=20=20=20=20=20=20o=20How=20to=20"concat"=20row=20values=20into=20= a=20single column?=20 =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20+=20From:=20Jesse,=20Rich __________________________________________________ Do=20You=20Yahoo!? Tired=20of=20spam?=20=20Yahoo!=20Mail=20has=20the=20best=20spam=20protecti= on=20around http://mail.yahoo.com -- http://www.freelists.org/webpage/oracle-l --=20 Welcome! Home:=20http://www.IamDBA.com=20 PLEASE=20NOTE:=20THE=20ABOVE=20MESSAGE=20WAS=20RECEIVED=20FROM=20THE=20INT= ERNET. On=20entering=20the=20GSI,=20this=20email=20was=20scanned=20for=20viruses=20= by=20the=20Government=20Secure=20Intranet=20(GSi)=20virus=20scanning=20ser= vice=20supplied=20exclusively=20by=20Cable=20&=20Wireless=20in=20partnersh= ip=20with=20MessageLabs. In=20case=20of=20problems,=20please=20call=20your=20organisational=20IT=20= Helpdesk. The=20MessageLabs=20Anti=20Virus=20Service=20is=20the=20first=20managed=20= service=20to=20achieve=20the=20CSIA=20Claims=20Tested=20Mark=20(CCTM=20Cer= tificate=20Number=202006/04/0007),=20the=20UK=20Government=20quality=20mar= k=20initiative=20for=20information=20security=20products=20and=20services.= =20For=20more=20information=20about=20this=20please=20visit=20www.cctmark.= gov.uk The=20original=20of=20this=20email=20was=20scanned=20for=20viruses=20by=20= Government=20Secure=20Intranet=20(GSi)=20=20virus=20scanning=20service=20s= upplied=20exclusively=20by=20Cable=20&=20Wireless=20in=20partnership=20wit= h=20MessageLabs. On=20leaving=20the=20GSI=20this=20email=20was=20certified=20virus=20free. The=20MessageLabs=20Anti=20Virus=20Service=20is=20the=20first=20managed=20= service=20to=20achieve=20the=20CSIA=20Claims=20Tested=20Mark=20(CCTM=20Cer= tificate=20Number=202006/04/0007),=20the=20UK=20Government=20quality=20mar= k=20initiative=20for=20information=20security=20products=20and=20services.= =20=20For=20more=20information=20about=20this=20please=20visit=20www.cctma= rk.gov.uk ------_=_NextPart_001_01C6BAFF.BC10E409 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable =
Search=20 Asktom=20for=20STRAGG. A=20fantastic=20user=20defined=20aggregate=20f= unction,=20well=20worth=20 a=20public=20execute
 
Anthony
=20=20-----Original=20Message-----
From:=20 =20=20oracle-l-bounce@freelists.org=20[mailto:oracle-l-bounce@freelists.or= g]On=20 =20=20Behalf=20Of=20Lou=20Fangxin
Sent:=2008=20August=202006= =2016:26
To:=20 =20=20saibabu_d@yahoo.com
Cc:=20oracle-l@freelists.org
Sub= ject:=20 =20=20Re:=20How=20to=20"concat"=20row=20values=20into=20a=20single=20colum= n?

if=20 =20=20running=20on=209i=20or=20above,=20PIPELINE=20function=20can=20be=20u= sed=20to=20do=20this.

=20=20
On=207/25/06,=20Saibabu=20 =20=20Devabhaktuni=20<saibabu_d@yahoo.com>=20wr= ote:=20 =20=20How=20 =20=20=20=20about=20something=20like=20this:

create=20table=20test1= =20as=20select=20*=20from=20 =20=20=20=20dba_objects;

create=20or=20replace=20function=20test_f=20= (vid=20in=20varchar2)=20 =20=20=20=20
return=20varchar2
as
vout=20varchar2(50);
beginfor=20i=20in=20(select=20 =20=20=20=20object_id=20from=20test1=20where
object_name=3Dvid)=20loop<= BR>vout=20:=3D=20vout||'=20 =20=20=20=20'||i.object_id;
end=20loop;
return=20vout;
end;
/<= BR>
select=20 =20=20=20=20distinct=20object_name,=20test_f(object_name)=20
from=20tes= t1=20where=20 =20=20=20=20object_name=3D'TEST_OBJ';
OBJECT_NAME   &nbs= p;            =     NAME
------------------------------
--------= --------------------------------
TEST_OBJ     =             &n= bsp;    =20 =20=20=20=202101=2024011=2024075=20241258

You=20can=20use=20similar= =20functionality=20in=20your=20 =20=20=20=20case.

Thanks,
Sai.
http://sai-oracle.blog= spot.com


RE:=20 =20=20=20=20How=20to=20"concat"=20row=20values=20into=20a=20single=20colum= n?=20

  =20*=20 =20=20=20=20From:=20"Mark=20W.=20Farnham"=20<mwf@xxxxxxxx>
 =  =20*=20To:=20 =20=20=20=20<Rich.Jesse@xxxxxx>,
<oracle-l@xxxxxxxxxxxxx>  =20 =20=20=20=20*=20Date:=20Mon,=2024=20Jul=202006=2016:58:42=20-0400

D= o=20you=20mean:

select=20 =20=20=20=20to_char(=20 =20=20=20=20a.at_id)||'
'||to_char(alarm_interval)||'
'||c3.email_ad= dress||'=20 =20=20=20=20'||c4.email_address
from=20alarm_type=20a,=20contact=20c3,=20= contact=20c4
where=20 =20=20=20=20a.at_id=20=3D=201
    =20and=20c3.at_id= =20=3D=20 =20=20=20=20a.at_id
    =20and=20c4.at_id=20=3D=20 =20=20=20=20a.at_id
    =20and=20c3.contact_id=20=3D= =20 =20=20=20=203
    =20and=20c4.contact_id=20=3D=204;=

?

If=20 =20=20=20=20you're=20at=20a=20sufficient=20RDBMS=20Level,=20you=20mightprofit=20from

from=20 =20=20=20=20alarm_type=20a,=20(select=20at_id,email_address=20from
cont= act=20where=20contact_id=20 =20=20=20=20
=3D=203)=20c3,=20(select=20at_id,email_address=20from=20co= ntact
where=20contact_id=20=3D=20 =20=20=20=204)=20c4

and=20losing=20the=20last=20two=20ands.=20(Alth= ough=20in=20a=20perfect
CBO=20 =20=20=20=20world=20it=20wouldn't
matter).

-----Original=20Messa= ge-----
From:=20 =20=20=20=20oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@= xxxxxxxxxxxxx]On
Behalf=20 =20=20=20=20Of=20Jesse,=20Rich
Sent:=20Monday,=20July=2024,=202006=202:= 34=20PM
To:=20 =20=20=20=20oracle-l@xxxxxxxxxxxxx
Subject:=20How=20to=20"concat"=20row= =20values=20into=20a=20 =20=20=20=20single
column?

Arg.  I=20know=20I've=20see= n=20this=20before=20and=20I=20 =20=20=20=20may=20have
actually=20done=20it
before,=20but=20I'll=20b= e=20darned=20if=20I=20can=20remember=20 =20=20=20=20how.

In=209.2,=20I=20have=20two=20tables=20kinda=20like= =20these:=20

CREATE=20TABLE=20 =20=20=20=20ALARM_TYPE
(
AT_ID      &n= bsp;   =20 =20=20=20=20NUMBER         &n= bsp;           &nbs= p;  NOT
NULL,
ALARM_INTERVAL  NUMBER  =             &n= bsp;         NOT
NULL
)=
/

Insert=20 =20=20=20=20into=20ALARM_TYPE
  (AT_ID,=20ALARM_INTERVAL)=20 =20=20=20=20
Values
  (1,=2010);
COMMIT;

CREATE=20= TABLE=20 =20=20=20=20CONTACT
(
CONTACT_ID      =     =20 =20=20=20=20NUMBER         &n= bsp;        =20 =20=20=20=20NOT
NULL,
AT_ID       = ;         NUMBER  &= nbsp;           &nb= sp;   =20 =20=20=20=20NOT
NULL,
EMAIL_ADDRESS     &nb= sp;  VARCHAR2(200)       &nbs= p;    NOT=20 =20=20=20=20
NULL
)
/

Insert=20into=20CONTACT
 &nb= sp;(CONTACT_ID,=20 =20=20=20=20AT_ID,=20EMAIL_ADDRESS)
Values
  (3,=201,=20 =20=20=20=20'someone@xxxxxxxxxx');
Insert=20into=20CONTACT
 &nb= sp;(CONTACT_ID,=20 =20=20=20=20AT_ID,=20EMAIL_ADDRESS)
Values
  (4,=201,=20'e= lse@xxxxxxxxxx=20 =20=20=20=20');
COMMIT;

I=20would=20like=20a=20query=20to=20retu= rn=20a=20single=20 =20=20=20=20row:

1      =20 =20=20=20=2010      someone@xxxxxxxxxx,=20 =20=20=20=20else@xxxxxxxxxx

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

Of=20course,=20this=20is=20way-oversimplifi= ed=20for=20the=20sake=20 =20=20=20=20of
brevity.

Anyone???

TIA!
Rich
--
<= A=20 =20=20=20=20href=3D"http://www.freelists.org/webpage/oracle-l">http://www.= freelists.org/webpage/oracle-l=20 =20=20=20=20


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


  =20 =20=20=20=20*=20References:
       &= nbsp;=20o=20How=20to=20 =20=20=20=20"concat"=20row=20values=20into=20a=20single
column?=20 =20=20=20=20
         &nbs= p;    =20 =20=20=20=20+=20From:=20Jesse,=20 =20=20=20=20Rich


______________________________________________= ____
Do=20You=20 =20=20=20=20Yahoo!?
Tired=20of=20spam?  Yahoo!=20Mail=20has=20= the=20best=20spam=20 =20=20=20=20protection=20around
http://mail.yahoo.com
--=
http://www.= freelists.org/webpage/oracle-l




--=20
Welcome!
Home:=20http://www.IamDBA.com=20
PLEAS= E=20NOTE:=20THE=20 =20=20ABOVE=20MESSAGE=20WAS=20RECEIVED=20FROM=20THE=20INTERNET.
On=20en= tering=20the=20GSI,=20this=20 =20=20email=20was=20scanned=20for=20viruses=20by=20the=20Government=20Secu= re=20Intranet=20(GSi)=20virus=20 =20=20scanning=20service=20supplied=20exclusively=20by=20Cable=20&=20W= ireless=20in=20partnership=20 =20=20with=20MessageLabs.
In=20case=20of=20problems,=20please=20call=20= your=20organisational=20IT=20 =20=20Helpdesk.
The=20MessageLabs=20Anti=20Virus=20Service=20is=20the=20= first=20managed=20service=20 =20=20to=20achieve=20the=20CSIA=20Claims=20Tested=20Mark=20(CCTM=20Certifi= cate=20Number=202006/04/0007),=20 =20=20the=20UK=20Government=20quality=20mark=20initiative=20for=20informat= ion=20security=20products=20 =20=20and=20services.=20For=20more=20information=20about=20this=20please=20= visit=20 =20=20www.cctmark.gov.uk

The=20original=20of=20this=20email=20was=20scanned=20for=20viruses=20by=20= Government=20Secure=20Intranet=20(GSi)=20=20virus=20scanning=20service=20s= upplied=20exclusively=20by=20Cable=20&=20Wireless=20in=20partnership=20wit= h=20MessageLabs.
On=20leaving=20the=20GSI=20this=20email=20was=20certified=20virus=20free.<= BR> The=20MessageLabs=20Anti=20Virus=20Service=20is=20the=20first=20managed=20= service=20to=20achieve=20the=20CSIA=20Claims=20Tested=20Mark=20(CCTM=20Cer= tificate=20Number=202006/04/0007),=20the=20UK=20Government=20quality=20mar= k=20initiative=20for=20information=20security=20products=20and=20services.= =20=20For=20more=20information=20about=20this=20please=20visit=20www.cctma= rk.gov.uk
------_=_NextPart_001_01C6BAFF.BC10E409-- -- http://www.freelists.org/webpage/oracle-l