| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: JOIN question - you CAN do it
Here's another way...
table gb is just a number of rows of (code, text) pairs with 'TRANSTYPE' as one code.
select distinct gb1.code , gb1.text "1", gb2.text "2", gb3.text "3", gb4.text "4", gb5.text "5", gb6.text "6"
from ( select * from ( select rownum "RN" , gb.* from gb ) where rn = 1) gb1,
( select * from ( select rownum "RN" , gb.* from gb ) where rn = 2) gb2,
( select * from ( select rownum "RN" , gb.* from gb ) where rn = 3) gb3,
( select * from ( select rownum "RN" , gb.* from gb ) where rn = 4) gb4,
( select * from ( select rownum "RN" , gb.* from gb ) where rn = 5) gb5,
( select * from ( select rownum "RN" , gb.* from gb ) where rn = 6) gb6
where gb1.code = gb2.code(+)
and gb2.code = gb3.code(+)
and gb3.code = gb4.code(+)
regards
George
> -----Original Message-----
> From: "Elliott, Patrick" <Patrick.Elliott_at_bestbuy.com>
> [mailto:Patrick.Elliott_at_bestbuy.com]
> Sent: Tuesday, June 13, 2000 10:58 PM
> To: SMTP_at_INL002@Servers["Multiple recipients of list ORACLE-L"
> <ORACLE-L_at_fatcity.com>]
> Subject: RE: JOIN question - you can't do it
>
>
> The only way I know of to do this is to create a stored function that
> returns a varchar2. This isn't quite what you want, but it
> comes close.
>
> CREATE OR REPLACE FUNCTION concat_code (cv_docnum number)
> lv_codes VARCHAR2(255) := '';
> CURSOR c1 IS
> SELECT code
> FROM codes b
> WHERE a.docnum = cv_docnum
> AND b.docnum = a.docnum;
> BEGIN
> FOR r1 IN c1 LOOP
> lv_codes := lv_codes || ' ' || r1.code;
> END LOOP;
> RETURN lv_codes;
> END;
>
> You can then include this function in a select statement.
>
> SELECT text, concat_code(docnum)
> FROM main;
>
> > -----Original Message-----
> > From: Glenn Travis [SMTP:Glenn.Travis_at_wcom.com]
> > Sent: Tuesday, June 13, 2000 2:21 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: JOIN question - you can't do it
> >
> >
> >
> > > -----Original Message-----
> > > From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf
> Of Markellos
> > > Diorinos
> > > Sent: Tuesday, June 13, 2000 9:08 AM
> > > To: Multiple recipients of list ORACLE-L
> > > Subject: RE: JOIN question - you can't do it
> > >
> > >
> > >
> > > > Assume the following tables:
> > > >
> > > > MAIN(docnum number primary key,text varchar2);
> > > > CODES(docnum number ,code varchar2);
> > > >
> > > > The CODES table contains multiple rows with the same docnum.
> > > > I want to fetch the "text" and all "code" columns with
> the same docnum
> > > > in a single row.
> > >
> > > My understanding is that this is principally IMPOSSIBLE in any
> > > SQL dialect.
> > >
> > > You need a result set with an unknown number of columns, i.e. one
> > > row can be 1x text, 3x code, the next one could be 1x text, 10x
> > > code. There is no support for this in SQL.
> > >
> > > There are a few features for 'turning arround' a table (i.e.
> > > exchanging rows/columns) but this will no do in your case.
> > >
> >
> > Can you explain these features in more detail?
> >
> > Given table A:
> >
> > col1 col2
> > ---- ----
> > Manager John
> > Executive Mark
> > Customer James
> > President Tom
> >
> > Provide a single query (or a procedure) which returns;
> >
> > Manager Executive Customer President
> > ------- --------- -------- ---------
> > John Mark James Tom
> >
> > What happens when you have table A: ?
> >
> > col1 col2
> > ---- ----
> > Manager John
> > Executive Mark
> > Customer James
> > President Tom
> > Customer Jim
> >
> > This is a simple example. A more realistic example would
> have multiple
> > tables and col1 would be the code value describing the
> 'position' and a
> > joinback needed to get the columns headings (Manager, ...)
> in the output
> > row.
> >
> > I've seen this asked before but cannot locate the
> solutions. any ideas?
> >
> > ____________
> > Glenn Travis
> > MCI WorldCom
> > Enterprise Systems Development - DBA
> > Glenn.Travis_at_wcom.com
> >
> >
> > > Anybody else knows something that I am missing?
> > >
> > >
> > > rgds
> > > markellos
> > >
> > > ----
> > > Markellos J Diorinos
> > > Intracom R&D
> > > email : mdio_at_intracom.gr
> > > tel : ++30-1-6690308
> > > fax : ++30-1-6860312
> > >
> > > --
> > > Author: Markellos Diorinos
> > > INET: mdio_at_intracom.gr
> > >
> > > 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).
> > >
> >
> > --
> > Author: Glenn Travis
> > INET: Glenn.Travis_at_wcom.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).
> --
> Author: Elliott, Patrick
> INET: Patrick.Elliott_at_bestbuy.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
Received on Wed Jun 14 2000 - 04:43:50 CDT
 |
 |