Well, you could get a very wide row as a result. You can get that row with all combinations covered but there is a question of how useful it could be. Anyway, with your sample data:
WITH
staff AS
(
Select 1234 "ID", 'Miller' "SURNAME" From Dual Union All
Select 4567 "UID", 'Jake' "SURNAME" From Dual
),
position AS
(
Select 1234 "ID", 'Engineer' "POS_ROLE", 'JAN' "POS_FROM", 'DEC' "POS_TO" From Dual Union All
Select 1234 "ID", 'Worker' "POS_ROLE", 'JAN' "POS_FROM", 'DEC' "POS_TO" From Dual
),
knowledge AS
(
Select 1234 "ID", 'Cert 1' "CERT", 'JAN' "CERT_FROM", 'DEC' "CERT_TO" From Dual Union All
Select 1234 "ID", 'Cert 2' "CERT", 'JAN' "CERT_FROM", 'FEB' "CERT_TO" From Dual
)
SQL getting the full row (with all combinations covered):
SELECT
ID, SURNAME "Surname",
A_ROLE "Role", A_P_FROM "From", A_P_TO "To",
B_ROLE "Role", B_P_FROM "From", B_P_TO "To",
C_ROLE "Role", C_P_FROM "From", C_P_TO "To",
D_ROLE "Role", D_P_FROM "From", D_P_TO "To",
--
A_CERT "Cert", A_C_FROM "From", A_C_TO "To",
B_CERT "Cert", B_C_FROM "From", B_C_TO "To",
C_CERT "Cert", C_C_FROM "From", C_C_TO "To",
D_CERT "Cert", D_C_FROM "From", D_C_TO "To"
FROM
(
Select DISTINCT
ROWNUM "IDX",
s.ID, s.SURNAME "SURNAME",
p.POS_ROLE "POS", k.CERT "CERT",
p.POS_FROM "P_FROM", p.POS_TO "P_TO",
k.CERT_FROM "C_FROM", k.CERT_TO "C_TO"
From
staff s
Inner Join
position p ON (p.ID = s.ID)
Inner Join
knowledge k ON (k.ID = s.ID)
Where s.ID = 1234
Order By s.ID, p.POS_ROLE, k.CERT
)
PIVOT (
Max(POS) "ROLE", Max(P_FROM) "P_FROM", Max(P_TO) "P_TO",
Max(CERT) "CERT", Max(C_FROM) "C_FROM", Max(C_TO) "C_TO"
FOR (IDX) IN(1 "A", 2 "B", 3 "C", 4 "D")
)
/* R e s u l t :
ID Surname Role From To Role From To Role From To Role From To Cert From To Cert From To Cert From To Cert From To
---------- ------- -------- ---- --- -------- ---- --- -------- ---- --- -------- ---- --- ------ ---- --- ------ ---- --- ------ ---- --- ------ ---- ---
1234 Miller Worker JAN DEC Engineer JAN DEC Worker JAN DEC Engineer JAN DEC Cert 1 JAN DEC Cert 1 JAN DEC Cert 2 JAN FEB Cert 2 JAN FEB
*/
There is a lot of repeating values because the sample data is repeatig too. In the innermost query there is ROWNUM pseudo column used in Pivot's FOR list of values and aliases. It could be even widened if needed.
Because of different levels of pivoting (ordering also could affect this) and that repeating data for your exact expecting result your selection list in outermost SQL should take A and B ROLES, plus A and C CERTS.
SELECT
ID, SURNAME "Surname",
A_ROLE "Role", A_P_FROM "From", A_P_TO "To",
B_ROLE "Role", B_P_FROM "From", B_P_TO "To",
--
A_CERT "Cert", A_C_FROM "From", A_C_TO "To",
C_CERT "Cert", C_C_FROM "From", C_C_TO "To"
FROM
...
/* R e s u l t :
ID Surname Role From To Role From To Cert From To Cert From To
---------- ------- -------- ---- --- -------- ---- --- ------ ---- --- ------ ---- ---
1234 Miller Worker JAN DEC Engineer JAN DEC Cert 1 JAN DEC Cert 2 JAN FEB
*/
This is caused by ROWNUM and ordering - one of the columns would always be infront of another either you sort it by role, cert or cert, role...
There is a lot of questions unanswerd that could pop up like processing IDs separately not to confuze the Pivot's Max() function... Anyway, you could try to adjust it to your actual data.
Regards...
