RE: SQL Query to combine multiple rows into 1 ?

From: Jack C. Applewhite <japplewhite_at_inetprofit.com>
Date: Fri, 18 May 2001 10:13:02 -0700
Message-ID: <F001.00307C0E.20010518091114@fatcity.com>

Todd,

A quick idea off the top of my head (where there's not much anyway!)... Use the table in the From clause as many times as the most occurrences of ID. Then use Outer Joins to construct your rows. This will be OK as long as the highest count of ID is not too large. (This idea may not even work and will require some more thought and "fleshing out". I fear that this "quickie" suggestion may produce duplicates.)

Select a.ID,a.Relation || b.Relation || c.Relation || d.Relation || e.Relation ...
>From MyTable a

      ,MyTable b
      ,MyTable c
      ,MyTable d
      ,MyTable e
      ...
Where a.ID        = b.ID (+)
And   a.Relation <> b.Relation (+)
And   a.ID        = c.ID (+)
And   a.Relation <> c.Relation (+)
And   a.ID        = d.ID (+)
And   a.Relation <> d.Relation (+)
And   a.ID        = e.ID (+)

And a.Relation <> e.Relation (+)
...
;

Hope it at least gives you a start.

I also just thought about creating some views (and views on those views) that use Minus, Min() or Max(), and RowID to eliminate all but the 1st, 2nd, 3rd, etc. Relation for each ID, but it needs some thought, too.

Gotta go.

Jack

Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
japplewhite_at_inetprofit.com
(512)327-9068

-----Original Message-----
Todd
Sent: Friday, May 18, 2001 9:57 AM
To: Multiple recipients of list 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>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack C. Applewhite
  INET: japplewhite_at_inetprofit.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).

Received on Fri May 18 2001 - 12:13:02 CDT