RE: JOIN question - you can't do it

From: Glenn Travis <Glenn.Travis_at_wcom.com>
Date: Tue, 13 Jun 2000 14:03:46 -0400
Message-Id: <10527.109015@fatcity.com>

> -----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
Received on Tue Jun 13 2000 - 13:03:46 CDT