SQL查询如何实现行转列？现有分组汇总结果需转置为横向展示

通用兼容方案（全数据库支持） #

使用条件聚合逻辑实现，兼容性最强，无需考虑数据库语法差异，修改后的SQL如下：

SELECT 
    SUM(CASE WHEN element_name = 'AH Housing Allowance' THEN result_value ELSE 0 END) AS `AH Housing Allowance`,
    SUM(CASE WHEN element_name = 'AH Basic Salary' THEN result_value ELSE 0 END) AS `AH Basic Salary`,
    SUM(CASE WHEN element_name = 'AH Transport Allowance' THEN result_value ELSE 0 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')

修改逻辑说明：

• 移除原SQL末尾的GROUP BY element_name子句
• 针对每个需要转为列的element_name值，用CASE WHEN判断后单独求和，设置对应列名即可

专用语法方案（支持PIVOT函数的数据库可用） #

如果你使用的是Oracle 11g+、SQL Server、PostgreSQL 12+等支持原生行转列函数的数据库，可以用PIVOT语法简化写法：

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 element_name in ('AH Basic Salary','AH Housing Allowance','AH Transport Allowance')
)
PIVOT (
    SUM(result_value)
    FOR element_name IN (
        'AH Housing Allowance' AS "AH Housing Allowance",
        'AH Basic Salary' AS "AH Basic Salary",
        'AH Transport Allowance' AS "AH Transport Allowance"
    )
)

内容的提问来源于stack exchange，提问作者Abinnaya