I think its easier if you do it cross-tab
AUS DAL DAL AUS
Is that acceptable?
Or just select
AUS DAL
If it also has a DAL AUS
Are either of those metods acceptable? If so, pick one and Ill show you how to do it.
From: Deshpande, Kirti [EMAIL PROTECTED]
Date: 2003/03/13 Thu AM 11:19:15
Title: Re: Corrected SQL Question...
SQL select A.c1, B.c2
2 from (select col1 c1, rownum r from tbl order by col1) A
3 , (select col2 c2, rownum r from tbl order by col2) b
4 where a.r = b.r
5 union
6 select B.c2, A.c1
7 from (select col1 c1, rownum r from tbl order by col1) A
8
I think those solutions should be acceptable.
Not sure if they are displaying any more information from the table. I was just given
the test table to get the SQL script working
Thanks a lot.
- Kirti
-Original Message-
Sent: Thursday, March 13, 2003 11:04 AM
To: Multiple
Kirti,
Can you explain the required result order? It looks random to me - or like
one of the tests we were forced to take in High School.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Thursday, March 13, 2003 9:31 AM
To: Multiple recipients of list ORACLE-L
Tom,
They wanted to 'pair up' the contents from c1 and c2.
Those are supposed to be 3 char Airport codes. DAL-AUS followed by AUS-DAL (or
vice-versa).
That's all I was told.
Thanks.
- Kirti
-Original Message-
Sent: Thursday, March 13, 2003 11:55 AM
To: Multiple recipients of list
Not quite random. Note that the value is field 1 of the first record is
the value in field 2 in the second. It looks like they want to pair up the
cities if they appear in both columns.
i.e. Since Dallas is in column 1 with Austin in Column 2 in one record,
and Dallas is in Column 2 with
Title: RE: Corrected SQL Question...
(see answer below)
-Original Message-
From: Deshpande, Kirti [mailto:[EMAIL PROTECTED]]
Here is the test data:
SQL select c1,c2 from cp;
C1 C2
--- ---
AUS DAL
AUS HOU
DAL AUS
DAL HOU
DAL LIT
DAL XYZ
HOU AUS
HOU DAL
HOU LIT
Do mean something like this? It would be interesting to see if this could
be done with some kind of tree walk.
1* select a.c1, a.c2, b.c1, b.c2 from crap a, crap b where a.c2 = b.c1 and
b.c2 = a.c1
SQL /
C1C2C1C2
- - - -
DAL AUS AUS DAL
HOU AUS AUS HOU
Title: RE: Corrected SQL Question...
Jacques,
Thanks a bunch.
Elegance was not one of the requirements ;)
Cheers!
-
Kirti
-Original Message-From: Jacques Kilchoer
[mailto:[EMAIL PROTECTED]Sent: Thursday, March 13, 2003
12:53 PMTo: '[EMAIL PROTECTED]'Cc: Deshpande
Questions I would have for those who wrote the requirements:
Of possible combinations of the form ABC XYZ XYZ ABC, which do they want?
As can be seen from the answers sent to the list, there is more than one set
of responses that give this pattern. If they only want half of the
possible
All they wanted was to pair up those city codes.
DAL -- AUS followed by AUS -- DAL,
AUS -- HOU followed by HOU -- AUS
etc...
and on separate lines.
So, cross-tab did not have the right format.
I sent them Jacques Kilchoer's solution (he also sent me a simplified one, without the
UNION),
: Corrected SQL Question...
All they wanted was to pair up those city codes.
DAL -- AUS followed by AUS -- DAL,
AUS -- HOU followed by HOU -- AUS
etc...
and on separate lines.
So, cross-tab did not have the right format.
I sent them Jacques Kilchoer's solution (he also sent me
Kirti,
What about solution suggested by Stephane Faroult:
select *
from (select *
from T
connect by col1 = prior col2
and col1 col2) x
where rownum = (select count(*) from T)
/
?
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
- Original Message -
To: Multiple
Igor Neyman wrote:
Kirti,
What about solution suggested by Stephane Faroult:
select *
from (select *
from T
connect by col1 = prior col2
and col1 col2) x
where rownum = (select count(*) from T)
/
?
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
Igor,
I can
: Deshpande, Kirti [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 13, 2003 2:24 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Corrected SQL Question...
All they wanted was to pair up those city codes.
DAL -- AUS followed by AUS -- DAL,
AUS -- HOU followed by HOU -- AUS
etc
15 matches
Mail list logo
