SQL query to get sum of each element in seperate columns

Question

I had asked this question few days back but the solution did not work perfectly and hence I am asking this question with the new logic-

  SELECT   person_number,taxable_earn, Basic_Life,Tax_Units
      FROM   (SELECT     ppa.effective_date, 
                         pra.result_value , 
                         pra.elementname, 
                         prd.person_number 
                FROM     pay_pay_relationships_dn prd 
               INNER JOIN pay_payroll_rel_actions pra ON prd.payroll_relationship_id = pra.payroll_relationship_id 
               INNER JOIN pay_payroll_actions ppa ON pra.payroll_action_id = ppa.payroll_action_id 
               INNER JOIN pay_all_payrolls_f pap ON ppa.element_type_id = pap.element_type_id 
               WHERE ppa.payroll_id = pap.payroll_id
                 AND ppa.effective_date BETWEEN :p_start_date AND :p_end_date)
     PIVOT    (SUM(result_value )
               FOR elementname IN ('taxable_earn' taxable_earn, 'Basic_Life' Basic_Life, 'Tax_Units' Tax_Units))
           

The above query is giving me an output like -

   EMPLOYEENUMBER           taxable_earn  Basic_Life     Tax_Units
           10                         6.7                           
           10                                       7.8             
           10                                                       9.2

I want the output to look like -

     EMPLOYEENUMBER         taxable_earn  Basic_Life     Tax_Units
       10                       6.7         7.8             9.2
       
      

How can i tweak the above query to get this output ?

Answer 1

Just remove ppa.effective_date, from your nested subquery :

SELECT   person_number,taxable_earn, Basic_Life,Tax_Units
      FROM   (SELECT     
                         pra.result_value , 
                         pra.elementname, 
                         prd.person_number 
                FROM     pay_pay_relationships_dn prd 
               INNER JOIN pay_payroll_rel_actions pra ON prd.payroll_relationship_id = pra.payroll_relationship_id 
               INNER JOIN pay_payroll_actions ppa ON pra.payroll_action_id = ppa.payroll_action_id 
               INNER JOIN pay_all_payrolls_f pap ON ppa.element_type_id = pap.element_type_id 
               WHERE ppa.payroll_id = pap.payroll_id
                 AND ppa.effective_date BETWEEN :p_start_date AND :p_end_date)
     PIVOT    (SUM(result_value )
               FOR elementname IN ('taxable_earn' taxable_earn, 'Basic_Life' Basic_Life, 'Tax_Units' Tax_Units))