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

From: Ashton Anthony \(Mr A\) ERDU <Anthony.Ashton_at_dti.gsi.gov.uk>
Date: Tue, 8 Aug 2006 16:31:36 +0100
Message-ID: <972D83C792B76A47B176328380D8F6D415EE59@SDCPRV01.dti.local>

Search Asktom for STRAGG. A fantastic user defined aggregate function, well worth a public execute  

Anthony

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Lou Fangxin Sent: 08 August 2006 16:26
To: saibabu_d_at_yahoo.com
Cc: oracle-l_at_freelists.org
Subject: Re: How to "concat" row values into a single column?

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> 


--
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 
PLEASE NOTE: THE ABOVE MESSAGE WAS RECEIVED FROM THE INTERNET.
On entering the GSI, this email was scanned for viruses by the Government Secure Intranet (GSi) virus scanning service supplied exclusively by Cable & Wireless in partnership with MessageLabs.
In case of problems, please call your organisational IT Helpdesk.
The MessageLabs Anti Virus Service is the first managed service to achieve the CSIA Claims Tested Mark (CCTM Certificate Number 2006/04/0007), the UK Government quality mark initiative for information security products and services. For more information about this please visit www.cctmark.gov.uk



The original of this email was scanned for viruses by Government Secure Intranet (GSi)  virus scanning service supplied exclusively by Cable & Wireless in partnership with MessageLabs.
On leaving the GSI this email was certified virus free.
The MessageLabs Anti Virus Service is the first managed service to achieve the CSIA Claims Tested Mark (CCTM Certificate Number 2006/04/0007), the UK Government quality mark initiative for information security products and services.  For more information about this please visit www.cctmark.gov.uk
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Aug 08 2006 - 10:31:36 CDT