用于将行数转换为列的SQL查询

Question

我编写了以下查询-

SELECT element_name,
element_start_date,
element_end_date,
period_name,
element_classfication,
Person_number
from per_elements
where element_name in ('Employer Contribution','HSA Employee Contribution')

给出以下输出

Element_name                element_start_date          element_end_date            Period_name             element_classfication   Person_number   Amount                      
Employer Contribution       6/14/2020                   6/27/2020                   14 2020 Biweekly        Liability               100             38
HSA Employee Contribution   6/14/2020                   6/27/2020                   14 2020 Biweekly        Deduction               100             19

我是否可以更改上面的查询，以便获得以下输出-

element_start_date    element_end_date   Period_name        Person_number       Employer contribution amount    Employee contribution amount                        
6/14/2020             6/27/2020         14 2020 Biweekly    100                 38                              19  

我是否可以将单独的行转换为列，以便数量可以并排反映。

Answer 1

您可以按如下方式使用条件聚合：

SELECT element_start_date,
       element_end_date,
       period_name,
       Person_number,
       max(case when element_name = 'Employer Contribution' then amount end) 
         as employer_contribution_amount,
       max(case when element_name = 'HSA Employee Contribution' then amount end) 
         as employee_contribution_amount
    from per_elements
   where element_name in ('Employer Contribution','HSA Employee Contribution')
group by element_start_date,
         element_end_date,
         period_name,
         Person_number;

Answer 2

select *
from 
   (
   select Element_name,element_start_date,element_end_date,Period_name,Person_number,Amount
   from per_elements
   where element_name in ('Employer Contribution','HSA Employee Contribution')
   )
pivot (
  sum(amount)
  for Element_name in ('Employer Contribution','HSA Employee Contribution')
)
/