Using Pivot in Oracle SQL to dynamically show one or two columns in case multiple records are present

I am working in Oracle Fusion HCM and would like to create a query which pulls an employee's base data such as name, location, etc. We also want to include the managers. Our manager structure is as such so that there's 1 line manager and 1 to n (realistically not more than 3) matrix managers, named 'REVIEWER'.

I have a working code that fetches the data, but it gives issues when there's not exactly 2 managers. When there's 1, it shows the same name twice and if there's 3, there is one that is not shown.

Can anyone help me out on how to fetch the correct manager names without using the MIN/MAX aggregrates? My query is already fetching the correct data, but my pivot clause is not working correctly.

Select DISTINCT * from ( SELECT DISTINCT emplName.DISPLAY_NAME Worker_Name, INITCAP(loc.LOCATION_NAME) Location_Name, gra.NAME Grade_Name, hou.NAME Department_Name, ass.MANAGER_TYPE Manager_Type, mgr.DISPLAY_NAME Manager_Name, REPLACE(ctr.CONTRACT_END_DATE,'4712-12-31') Contract_End_Date, aa.ASSIGNMENT_NUMBER FROM PER_ALL_ASSIGNMENTS_M aa, PER_ASSIGNMENT_SUPERVISORS_F ass, PER_PERSON_NAMES_F emplName, PER_ALL_PEOPLE_F empl, PER_PERSON_NAMES_F mgr, HR_ORGANIZATION_UNITS hou, HR_LOCATIONS_ALL_F_VL loc, PER_GRADES_F_TL gra, PER_CONTRACTS_F ctr WHERE aa.ASSIGNMENT_ID (+) = ass.ASSIGNMENT_ID AND emplName.PERSON_ID = ass.PERSON_ID AND ass.MANAGER_ID = mgr.PERSON_ID AND empl.PERSON_ID = ass.PERSON_ID AND hou.ORGANIZATION_ID = aa.ORGANIZATION_ID AND loc.LOCATION_ID = aa.LOCATION_ID AND gra.GRADE_ID = aa.GRADE_ID AND ctr.CONTRACT_ID = aa.CONTRACT_ID AND aa.ASSIGNMENT_STATUS_TYPE = 'ACTIVE' AND to_char(ass.EFFECTIVE_END_DATE, 'DD/MM/YYYY') = '31/12/4712' AND to_char(aa.EFFECTIVE_END_DATE, 'DD/MM/YYYY') = '31/12/4712' AND to_char(ctr.EFFECTIVE_END_DATE, 'DD/MM/YYYY') = '31/12/4712' AND gra.SOURCE_LANG = 'US' AND gra.NAME in (:p_grade) AND hou.NAME in (:p_department) AND INITCAP(loc.LOCATION_NAME) in (:p_location) AND (ctr.CONTRACT_END_DATE <= (:p_contractenddate) OR (:p_contractenddate) is null) ) S Pivot ( MAX(Manager_Name) Manager1, MIN(Manager_Name) Manager2 for manager_type in ('LINE_MANAGER' as Line_Manager, 'REVIEWER' as Reviewer )) Piv 

The data regarding managers is recorded in PER_ASSIGNMENT_SUPERVISORS_F ass as follows:

Edit: Table formatting was broken

2 Answers

Use:

Select * from ( SELECT ass.assignment_id, ass.person_id, ass.MANAGER_TYPE Manager_Type, mgr.DISPLAY_NAME Manager_Name, ROW_NUMBER() OVER ( PARTITION BY ass.assignment_id, ass.person_id, ass.manager_type ORDER BY mgr.display_name ) AS rn FROM PER_ASSIGNMENT_SUPERVISORS_F ass INNER JOIN PER_PERSON_NAMES_F mgr ON (ass.MANAGER_ID = mgr.PERSON_ID) WHERE ass.EFFECTIVE_END_DATE = DATE '4712-12-31' ) PIVOT ( MAX(Manager_Name) for (manager_type, rn) in ( ('LINE_MANAGER', 1) as Line_Manager, ('REVIEWER', 1) as Reviewer1, ('REVIEWER', 2) as Reviewer2, ('REVIEWER', 3) as Reviewer3 ) ) 

Then join the rest of the tables to that pivoted query (rather than trying to join first and then pivot).

Which, for the (minimal) sample data:

CREATE TABLE PER_ASSIGNMENT_SUPERVISORS_F (assignment_id, person_id, manager_id, manager_type, effective_end_date) AS SELECT 1, 1, 2, 'LINE_MANAGER', DATE '4712-12-31' FROM DUAL UNION ALL SELECT 1, 1, 3, 'REVIEWER', DATE '4712-12-31' FROM DUAL UNION ALL SELECT 1, 1, 4, 'REVIEWER', DATE '4712-12-31' FROM DUAL UNION ALL SELECT 1, 1, 5, 'REVIEWER', DATE '4712-12-31' FROM DUAL UNION ALL SELECT 2, 2, 3, 'LINE_MANAGER', DATE '4712-12-31' FROM DUAL UNION ALL SELECT 2, 2, 4, 'REVIEWER', DATE '4712-12-31' FROM DUAL UNION ALL SELECT 2, 2, 5, 'REVIEWER', DATE '4712-12-31' FROM DUAL UNION ALL SELECT 3, 3, 4, 'LINE_MANAGER', DATE '4712-12-31' FROM DUAL UNION ALL SELECT 3, 3, 5, 'REVIEWER', DATE '4712-12-31' FROM DUAL UNION ALL SELECT 4, 4, 5, 'LINE_MANAGER', DATE '4712-12-31' FROM DUAL; CREATE TABLE PER_PERSON_NAMES_F (person_id, display_name) AS SELECT 1, 'Alice' FROM DUAL UNION ALL SELECT 2, 'Beryl' FROM DUAL UNION ALL SELECT 3, 'Carol' FROM DUAL UNION ALL SELECT 4, 'Debra' FROM DUAL UNION ALL SELECT 5, 'Emily' FROM DUAL; 

Outputs:

fiddle

Rewrote the query based on MT0s answer. For anyone interested in the end-result:

Select * from ( SELECT emplName.DISPLAY_NAME Worker_Name, INITCAP(loc.LOCATION_NAME) Location_Name, gra.NAME Grade_Name, hou.NAME Department_Name, ass.MANAGER_TYPE Manager_Type, mgr.DISPLAY_NAME Manager_Name, ROW_NUMBER() OVER ( PARTITION BY aa.ASSIGNMENT_NUMBER, ass.assignment_id, ass.person_id, gra.NAME, hou.NAME, ass.manager_type ORDER BY mgr.display_name ) AS rn, REPLACE(ctr.CONTRACT_END_DATE,'4712-12-31') Contract_End_Date, aa.ASSIGNMENT_NUMBER FROM PER_ALL_ASSIGNMENTS_F aa LEFT JOIN PER_ASSIGNMENT_SUPERVISORS_F ass ON (aa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID AND to_char(ass.EFFECTIVE_END_DATE, 'DD/MM/YYYY') = '31/12/4712') LEFT JOIN PER_PERSON_NAMES_F_V emplName ON (ass.PERSON_ID = emplName.PERSON_ID AND to_char(emplName.EFFECTIVE_END_DATE, 'DD/MM/YYYY') = '31/12/4712' AND emplName.NAME_TYPE = 'GLOBAL') LEFT JOIN PER_ALL_PEOPLE_F empl ON (empl.PERSON_ID = ass.PERSON_ID AND to_char(empl.EFFECTIVE_END_DATE, 'DD/MM/YYYY') = '31/12/4712') LEFT JOIN PER_PERSON_NAMES_F mgr ON (mgr.PERSON_ID = ass.MANAGER_ID AND to_char(mgr.EFFECTIVE_END_DATE, 'DD/MM/YYYY') = '31/12/4712' AND mgr.NAME_TYPE = 'GLOBAL') LEFT JOIN HR_ORGANIZATION_UNITS hou ON (hou.ORGANIZATION_ID = aa.ORGANIZATION_ID AND to_char(hou.DATE_TO, 'DD/MM/YYYY') = '31/12/4712') LEFT JOIN HR_LOCATIONS_ALL_F_VL loc ON (loc.LOCATION_ID = aa.LOCATION_ID AND to_char(loc.EFFECTIVE_END_DATE, 'DD/MM/YYYY') = '31/12/4712') LEFT JOIN PER_GRADES_F_TL gra ON (gra.GRADE_ID = aa.GRADE_ID AND gra.LANGUAGE = 'US' AND to_char(gra.EFFECTIVE_END_DATE, 'DD/MM/YYYY') = '31/12/4712') LEFT JOIN PER_CONTRACTS_F ctr ON (ctr.CONTRACT_ID = aa.CONTRACT_ID AND to_char(ctr.EFFECTIVE_END_DATE, 'DD/MM/YYYY') = '31/12/4712') WHERE 1=1 AND aa.ASSIGNMENT_STATUS_TYPE = 'ACTIVE' AND to_char(aa.EFFECTIVE_END_DATE, 'DD/MM/YYYY') = '31/12/4712' -- PARAMETERS AND gra.NAME in (:p_grade) AND hou.NAME in (:p_department) AND INITCAP(loc.LOCATION_NAME) in (:p_location) AND (ctr.CONTRACT_END_DATE <= (:p_contractenddate) OR (:p_contractenddate) is null) ) S Pivot ( MAX(Manager_Name) for (manager_type, rn) in ( ('LINE_MANAGER', 1) as Line_Manager, ('REVIEWER', 1) as Reviewer1, ('REVIEWER', 2) as Reviewer2, ('REVIEWER', 3) as Reviewer3 )) Piv