Query returning result in multiple rows for same employee

Question

I have a table with multiple values of elements for an element with corresponding input values:

EE   ELEMENT_NAME     RESULT_VALUE   EFFECTIVE DATE   INPUT VALUE
-----------------------------------------------------------------
12   Overtime           1000           10-APR-2023      earning
12   Overtime           10             10-APR-2023      Hours
12   REGULAR_RETRO      110            10-APR-2023      earning
11   REGULAR_RETRO      120            10-apr-2023      earning         

The required output is:

EE   REGULAR_RETRO   OVERTIME_PAID          OVERTIME_Hours_taken
----------------------------------------------------------------
12   110                1000                        10  
11   120                

This query is returning two rows:

SELECT
    ee person_number,
    SUM(CASE 
            WHEN peen.element_name IN 'REGULAR RETRO'
                THEN (RESULT_VALUE) 
        END) REGULAR_RETRO,
    SUM(CASE 
            WHEN peen.element_name IN 'OVERTIME' AND input_value = 'Hours'
                THEN (RESULT_VALUE)
        END) OVERTIME_hours,
    SUM(CASE 
            WHEN peen.element_name IN 'OVERTIME' AND input_value =  'earning'
                THEN (RESULT_VALUE) 
        END) OVERTIME_paid 
FROM
    PER_ALL_PEOPLE_F PAPF
    PER_ELEMENT_NAME PEEN,
    PEr_ELEMENT_TYPE PET
WHERE
    PAPF.PERSON_ID = PEEN.PERSON_ID
    AND PEEN.ELEMENT_ID = PET.ELEMENT_ID
    AND PET.INPUT_VALUE IN ('Earning','Hours')
    AND peen.element_name IN ('REGULAR RETRO', 'OVERTIME')
GROUP BY
    person_number

But this query is returning two rows for ee#12 since he has both input name - earning and hours but it should come in 1 row -

EE   REGULAR_RETRO   OVERTIME_PAID          OVERTIME_Hours_taken
-----------------------------------------------------------------
12   110                1000                        0 
12   110                 0                          10  
11   120        

Answer 1

If you have your data prepared as in the question - you could pivot rows into ccolumns, use case expressions to manage the data and aggregate grouped by EE to get the expected result:

WITH          --  Sample Data
    tbl (EE, ELEMENT_NAME, RESULT_VALUE, EFFECTIVE_DATE, INPUT_VALUE) AS
        (   Select 12, 'Overtime', 1000, To_Date('10-APR-2023', 'dd-MON-yyyy'), 'earning' From Dual Union All
            Select 12, 'Overtime',   10, To_Date('10-APR-2023', 'dd-MON-yyyy'), 'Hours' From Dual Union All
            Select 12, 'REGULAR_RETRO',  110, To_Date('10-APR-2023', 'dd-MON-yyyy'), 'earning' From Dual Union All
            Select 11, 'REGULAR_RETRO',  120, To_Date('10-APR-2023', 'dd-MON-yyyy'), 'earning' From Dual 
        )
--
--  M a i n   S Q L .
SELECT  EE, 
        Max(REGULAR_RETRO) "REGULAR_RETRO", 
        Max(OVERTIME_PAID) "OVERTIME_PAID",
        Max(OVERTIME_HOURS_TAKEN) "OVERTIME_HOURS_TAKEN"
FROM    (   Select  EE, 
                    Case When INPUT_VALUE = 'earning' THEN REGULAR_RETRO_PAID End "REGULAR_RETRO", 
                    Case When INPUT_VALUE = 'earning' THEN OVERTIME_PAID End "OVERTIME_PAID",
                    Case When INPUT_VALUE = 'Hours' THEN OVERTIME_HOURS_TAKEN End "OVERTIME_HOURS_TAKEN"
            From    tbl
            PIVOT   (   Sum(RESULT_VALUE) "PAID", Sum(RESULT_VALUE) "HOURS_TAKEN"
                  For ELEMENT_NAME IN('REGULAR_RETRO' "REGULAR_RETRO", 'Overtime' "OVERTIME" )
                )
        )
GROUP BY EE
ORDER BY EE Desc
--
--  R e s u l t :
--          EE REGULAR_RETRO OVERTIME_PAID OVERTIME_HOURS_TAKEN
--  ---------- ------------- ------------- --------------------
--          12           110          1000                   10 
--          11           120