RE: SQL Question

From: Aponte, Tony <AponteT_at_hsn.net>
Date: Thu, 4 Jan 2001 17:28:00 -0500
Message-Id: <10731.125787@fatcity.com>

This is what I was able to hash out for just the 2 legs shown in your sample data. It may get a little more complicated if there are mode than 2 legs on what looks like an travel itinerary.

create table leg (dt date,seqno number,area_from varchar2(10),area_to varchar2(10))

INSERT INTO leg VALUES (TO_DATE('20-jan','dd-mon'),1,'LONDON','NEW YORK')

INSERT INTO leg VALUES (TO_DATE('20-jan','dd-mon'),2,'NEW YORK','ATLANTA')

INSERT INTO leg VALUES (TO_DATE('21-jan','dd-mon'),1,'LONDON','PARIS')

INSERT INTO leg VALUES (TO_DATE('22-jan','dd-mon'),1,'NAIROBI','PARIS')

INSERT INTO leg VALUES (TO_DATE('22-jan','dd-mon'),2,'PARIS','NAIROBI')

INSERT INTO leg VALUES (TO_DATE('23-jan','dd-mon'),1,'ATLANTA','NEW YORK')

SELECT * FROM leg ORDER BY 1,2

SELECT
  TO_CHAR(l1.dt,'dd-Mon') DT
,l1.area_from C1
,l1.area_to C2
,l2.area_to C3

FROM
  leg l1
,leg l2

WHERE
  l1.dt = l2.dt (+)
AND
  l1.area_to = l2.area_from (+)
AND
  l1.seqno = 1
ORDER BY
  dt

DT C1 C2 C3

20-Jan  LONDON  NEW YORK    ATLANTA
21-Jan  LONDON  PARIS  
22-Jan  NAIROBI PARIS       NAIROBI
23-Jan  ATLANTA NEW YORK

-----Original Message-----
From: Johan Locke_at_i-Commerce Services [ mailto:Johan.Locke_at_za.didata.com <mailto:Johan.Locke_at_za.didata.com> ]
Sent: Thursday, January 04, 2001 2:11 AM To: Multiple recipients of list ORACLE-L Subject: SQL Question

Hi All

Got the following table

DATE          SEQNO         AREA_FROM       AREA_TO
----      -----         ---------   --------
20-JAN  1         LONDON  NEW YORK
20-JAN  2         NEW YORK        ATLANTA
21-JAN  1         LONDON  PARIS
22-JAN  1         NAIROBIPARIS
22-JAN  2         PARIS         NAIROBI
23-JAN  1         ATLANTANEW YORK


Now I need the following report based on the data:
DATE      AREAS              
----      -----              
20-JAN  LONDON - NEW YORK - ATLANTA
21-JAN  LONDON - PARIS

22-JAN NAIROBI - PARIS - NAIROBI
23-JAN ATLANTA - NEW YORK (1) Any Ideas on how to do this report without using Dynamic SQL (2) Is it possible to write a custom GROUP function in PL/SQL (i.e., something that would do a concatenate instead of a sum)?

Kind Regards

Johan Locke

http://www.JohanLocke.co.za <http://www.JohanLocke.co.za> Certified Oracle 8 & 8i DBA
Certified Oracle Developer

Dimension Data i-Commerce Internet Services Direct Line: +27 11 516 5343
mailto:Johan.Locke_at_za.didata.com <mailto:Johan.Locke_at_za.didata.com> http://www.didata.com <http://www.didata.com>

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
<http://www.orafaq.com> 
--
Author: Johan Locke_at_i-Commerce Services
  INET: Johan.Locke_at_za.didata.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

Received on Thu Jan 04 2001 - 16:28:00 CST