Re: SQL Query to combine multiple rows into 1 ?

The only way I know of to do this is with self-joins, and you have to have a known upper bound of the number of Relations per ID. An example
(warning, untested, off the top of my head):

select t1.id, t1.relation || ' ' || t2.relation || ' ' || t3.relation from relationtable t1, relationtable t2, relationtable t3 where t1.id = t2.id (+)
and t1.id = t3.id (+);

I've had problems in the past with multiple outer self-joins, but can't remember offhand how I fixed them...but hopefully this gives you and idea. Come to think of it, are outer joins ANSI SQL? I think so...

Wow, I'm prevaricating quite a bit today. Better quit while I'm ahead. :)

Diana Duncan
TITAN Technology Partners
One Copley Parkway, Ste 540
Morrisville, NC 27560
VM: 919.466.7337 x 316
F: 919.466.7427
E: Diana_Duncan_at_ttpartners.com

                                                                                       
                               
                    "Thompson, Todd"                                                   
                               
                    <tthompso_at_bcharr        To:     Multiple recipients of list 
ORACLE-L <ORACLE-L_at_fatcity.com>       
                    ispub.com>              cc:                                        
                               
                    Sent by:                Fax to:                                    
                               
                    root_at_fatcity.com        Subject:     SQL Query to combine multiple 
rows into 1 ?                  
                                                                                       
                               
                                                                                       
                               
                    05/18/2001 10:56                                                   
                               
                    AM                                                                 
                               
                    Please respond                                                     
                               
                    to ORACLE-L                                                        
                               
                                                                                       
                               
                                                                                       
                               

I've got a simple table with 2 columns: ID and Relation

E.X.
ID Relation
--- --------

123     Sam
123     Bobby
123     Dani
234     Mary
234     Cindy
345     Steve
456     Karen
456     Gary
456     Wayne

I'm trying to create a report using only ANSI SQL (no PL*SQL, or SQL*PLUS enhancements) to create the following output:

123 Sam Bobby Dani
234 Mary Cindy
345 Steve
456 Karen Gary Wayne

I'm totally stumped- Can anyone help??

Todd Thompson

Architecture and Technology Team
Harris Publishing
6363 Center Drive, Norfolk VA, 23502
Tel: 757.455.5434 Fax: 757.455.3010
Email: tthompso_at_bcharrispub.com <mailto:tthompso_at_bcharrispub.com>

(See attached file: winmail.dat)--

Please see the official ORACLE-L FAQ: http://www.orafaq.com

--
Author: Thompson, Todd
  INET: tthompso_at_bcharrispub.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).