0

how to convert row as column in sql query, below is my query..

SELECT  sum(result_value), element_name
        FROM pay_assignment_actions paa,pay_payroll_actions ppa,per_time_periods ptp,pay_run_results prr,
         pay_element_types_f ele,pay_run_result_values rrv,pay_input_values_f piv,pay_element_classifications pec
                   WHERE pec.classification_id = ele.classification_id AND paa.payroll_action_id = ppa.payroll_action_id
          AND ppa.time_period_id = ptp.time_period_id AND paa.assignment_action_id = prr.assignment_action_id
          AND prr.element_type_id = ele.element_type_id AND prr.run_result_id = rrv.run_result_id
          AND rrv.input_value_id = piv.input_value_id AND piv.name = 'Pay Value'
         -- AND paa.assignment_id = paaf.assignment_id 
          --AND :period_name BETWEEN TRUNC(ptp.start_date) and TRUNC(ptp.end_date)
          AND element_name in ('AH Basic Salary','AH Housing Allowance','AH Transport Allowance')
          GROUP BY element_name

output now i am getting:

salary                     element_name
------------               --------------------
682236308.24    AH Housing Allowance

3277904755.58   AH Basic Salary

346652883.22    AH Transport Allowance

but i want the result as below:


AH Housing Allowance   AH Basic Salary   AH Transport Allowance
--------------------   ----------------  ----------------------
682236308.24            3277904755.58     346652883.22
Koen Lostrie
  • 14,938
  • 2
  • 13
  • 19
Abinnaya
  • 203
  • 4
  • 26
  • Use the PIVOT function in Oracle - does exactly what you want. You can read up on it here: https://blogs.oracle.com/sql/post/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot – Koen Lostrie Sep 14 '21 at 07:00
  • 1
    Tip of today: Switch to modern, explicit `JOIN` syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed. – jarlh Sep 14 '21 at 07:04

2 Answers2

0

Perhaps helps PIVOT after your query (please eliminate GROUP BY and SUM):

PIVOT 
(
SUM(result_value) for element_name in('AH Basic Salary','AH Housing Allowance','AH Transport Allowance')
);

https://www.oracle.com/technical-resources/articles/database/sql-11g-pivot.html

SELECT * FROM (    
  SELECT result_value, element_name 
    FROM pay_assignment_actions paa,pay_payroll_actions ppa,per_time_periods ptp,pay_run_results prr, pay_element_types_f ele,pay_run_result_values rrv,pay_input_values_f piv,pay_element_classifications pec 
    WHERE pec.classification_id = ele.classification_id 
    AND paa.payroll_action_id = ppa.payroll_action_id 
    AND ppa.time_period_id = ptp.time_period_id 
    AND paa.assignment_action_id = prr.assignment_action_id 
    AND prr.element_type_id = ele.element_type_id 
    AND prr.run_result_id = rrv.run_result_id 
    AND rrv.input_value_id = piv.input_value_id 
    AND piv.name = 'Pay Value' 
    -- AND paa.assignment_id = paaf.assignment_id --AND :period_name BETWEEN TRUNC(ptp.start_date) and TRUNC(ptp.end_date) 
    AND element_name in ('AH Basic Salary','AH Housing Allowance','AH Transport Allowance')
)
PIVOT 
(
SUM(result_value) for element_name in('AH Basic Salary','AH Housing Allowance','AH Transport Allowance')
);

But I can't test it without Create statements.

Before Oracle 11 this can be the solution:

SELECT 
sum(case when element_name = 'AH Basic Salary' then result_value end) as AH Basic Salary,
sum(case when element_name = 'AH Housing Allowance' then result_value end) as AH Housing Allowance,
sum(case when element_name = 'AH Transport Allowance' then result_value end) as AH Transport Allowance
FROM pay_assignment_actions paa,pay_payroll_actions ppa,per_time_periods ptp,
pay_run_results prr, pay_element_types_f ele,pay_run_result_values rrv,
pay_input_values_f piv,pay_element_classifications pec 
WHERE pec.classification_id = ele.classification_id 
        AND paa.payroll_action_id = ppa.payroll_action_id 
        AND ppa.time_period_id = ptp.time_period_id 
        AND paa.assignment_action_id = prr.assignment_action_id 
        AND prr.element_type_id = ele.element_type_id 
        AND prr.run_result_id = rrv.run_result_id 
        AND rrv.input_value_id = piv.input_value_id 
        AND piv.name = 'Pay Value' 
        -- AND paa.assignment_id = paaf.assignment_id --AND :period_name BETWEEN TRUNC(ptp.start_date) and TRUNC(ptp.end_date) 
        AND element_name in ('AH Basic Salary','AH Housing Allowance','AH Transport Allowance')
László Tóth
  • 483
  • 5
  • 15
0

Use pivot in place of groupby, also in select statement use new column names

SELECT  [AH Basic Salary],[AH Housing Allowance],[AH Transport Allowance]
        FROM pay_assignment_actions paa,pay_payroll_actions ppa,per_time_periods ptp,pay_run_results prr,
         pay_element_types_f ele,pay_run_result_values rrv,pay_input_values_f piv,pay_element_classifications pec
                   WHERE pec.classification_id = ele.classification_id AND paa.payroll_action_id = ppa.payroll_action_id
          AND ppa.time_period_id = ptp.time_period_id AND paa.assignment_action_id = prr.assignment_action_id
          AND prr.element_type_id = ele.element_type_id AND prr.run_result_id = rrv.run_result_id
          AND rrv.input_value_id = piv.input_value_id AND piv.name = 'Pay Value'
         -- AND paa.assignment_id = paaf.assignment_id 
          --AND :period_name BETWEEN TRUNC(ptp.start_date) and TRUNC(ptp.end_date)
          AND element_name in ('AH Basic Salary','AH Housing Allowance','AH Transport Allowance')

          PIVOT 
(
SUM(result_value) FOR element_name IN ([AH Basic Salary],[AH Housing Allowance],[AH Transport Allowance])
)
AS PivotTable;
SAI PAVAN
  • 64
  • 2