如何在SQL查询中将行转换为列

Question

如何在SQL查询中将行转换为列，下面是我的查询。

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

输出现在我得到:

salary                     element_name
------------               --------------------
682236308.24    AH Housing Allowance
3277904755.58   AH Basic Salary
346652883.22    AH Transport Allowance

，但我想要的结果如下:

AH Housing Allowance   AH Basic Salary   AH Transport Allowance
--------------------   ----------------  ----------------------
682236308.24            3277904755.58     346652883.22

Answer 1

可能有助于在查询后进行PIVOT(请消除GROUP BY和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')
);

但是我不能在没有Create语句的情况下测试它。

在Oracle 11之前，这可能是解决方案:

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')

Answer 2

使用pivot代替groupby，同时在select语句中使用新的列名

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;