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: [EMAIL PROTECTED]
"Thompson, Todd"
<tthompso@bcharr To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
ispub.com> cc:
Sent by: Fax to:
[EMAIL PROTECTED] 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: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
(See attached file: winmail.dat)--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Thompson, Todd
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
winmail.dat
