Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Pivot query

Re: Pivot query

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 06 Dec 2005 11:20:15 -0800
Message-ID: <1133896795.822765@jetspin.drizzle.com>


Andyza wrote:
> A have a query that involves 3 tables:
>
> individuals (id(pk),personalid, i.firstname, i.surname)
> recruitments (r_id(pk),recruitmentstatus, statusdate, contractstatus,
> id(fk))
> terminations (t_id(pk),statusdate, statusreason, id(fk))
>
> The following query:
>
> SELECT i.personalid, i.firstname, i.surname,
> d.recruitmentstatus, d.statusdate, t.contractstatus,
> t.statusdate, t.statusreason
> FROM individuals i, recruitments d, terminations t
> WHERE ( (i.id = d.id(+))
> AND (i.id = t.id(+))
> AND (i.id = '1234')
> )
>
> produces this output:
>
> 7511015093123,Mark,Smith,Hired,2004/09/01,Resigned,2005/01/31
>
> How do I pivot the sql so that it transforms the output to:
>
> 7511015093123,Mark,Smith,Hired,2004/09/01,
> 7511015093123,Mark,Smith,Resigned,2005/01/31

Pipelined table function.

There is an example just like this on my web site: Morgan's Library at www.psoug.org. Look at the stock ticker demo which I pulled from OTN.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Tue Dec 06 2005 - 13:20:15 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US