Pivot query

From: Andyza <andyza_at_webmail.co.za>
Date: 6 Dec 2005 02:08:02 -0800
Message-ID: <1133863682.711294.115570@o13g2000cwo.googlegroups.com>

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 - 04:08:02 CST