Re: Pivot query

Why can't you use union for this.
Something like following.

SELECT i.personalid, i.firstname, i.surname, d.recruitmentstatus, d.statusdate from individuals i, recruitments d WHERE (i.id = d.id(+))
AND (i.id = '1234')
UNION
SELECT i.personalid, i.firstname, i.surname, t.contractstatus,t.statusdate from individuals i , terminations t where (i.id = t.id(+))
AND (i.id = '1234')

--Kiran.

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
Received on Tue Dec 06 2005 - 06:37:51 CST