sql查询将行转换为列

rvpgvaaj 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(407)

我写了下面的查询-

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

我可以将单独的行转换为列，这样就可以并排反映数量。

sql oracle oracle-sqldeveloper plsqldeveloper

来源：https://stackoverflow.com/questions/62836002/sql-query-to-convert-rows-amount-to-column

2条答案

按热度按时间

yks3o0rb1#

可以使用条件聚合，如下所示：

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;

赞(0）回复(0）举报 2021-07-24

u0njafvf2#

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

赞(0）回复(0）举报 2021-07-24

我来回答

sql查询将行转换为列

2条答案

相关问题

热门标签

最新问答