Oracle 分组 + 行转列操作_oracle 分组后行转列-CSDN博客

本文链接：https://blog.csdn.net/yin_jw/article/details/7620146

本文介绍了一种Oracle数据库中结合分组和行转列的方法，通过group by对tradeMonth（月份）、brokerBranch（营业部号）、userType（用户类型）进行数据汇总，并利用case when then实现数据的行转列转换，详细展示了如何处理原始数据以获取所需结果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、原始数据（这个数据是下面sql 查出的数据）

月份	营业部代码	用户类型	开户奖调整	提成调整	薪酬支出调整合计
...	...	...	...	...	...

select a.trademonth tradeMonth,
       a.broker_branch brokerBranch,
       decode(b.user_type, '3', '2', '5', '2', b.user_type) userType,
       a.margin_trade_tc_adj tc,
       a.open_bal_money_adj openBalMoneyAdj,
       a.broker_branch_sum brokerBranchSum

  from rzrq.tb_rzrq_cust_martc_cross a
 inner join xc.tb_h_snap_user b on a.user_id = b.user_id
                               and a.trademonth = b.archive_date
                               and b.user_type in ('0', '1', '3', '4', '5')
 where a.cust_branch <> a.broker_branch
union all
select c.trademonth tradeMonth,
       c.broker_branch brokerBranch,
       decode(d.user_type, '3', '2', '5', '2', d.user_type) userType,
       c.normal_trade_tc_adj tc,
       0.0000 openBalMoneyAdj,
       c.broker_branch_sum brokerBranchSum

  from rzrq.tb_rzrq_cust_nortc_cross c
 inner join xc.tb_h_snap_user d on c.user_id = d.user_id
                               and c.trademonth = d.archive_date
                               and d.user_type in ('0', '1', '3', '4', '5')
 where c.cust_branch <> c.broker_branch

上面sql作为下面sql的子查询，进行数据的分组汇总，行转列

select t.tradeMonth,
       t.brokerBranch,
       t.userType,
       sum(case t.userType when '0' then t.tc else 0 end) 客户经理提成调整,
       sum(case t.userType when '2' then t.tc else 0 end) 基础岗提成调整,
       sum(case t.userType when '1' then t.tc else 0 end) 经纪人提成调整,
       sum(case t.userType when '4' then t.tc else 0 end) 业务部提成调整,
       sum(openBalMoneyAdj) openBalMoneyAdjSum,
       sum(brokerBranchSum) expAdjustSum

  from (select a.trademonth          tradeMonth,
               a.broker_branch       brokerBranch,
               decode (b.user_type,'3','2','5','2',b.user_type )userType,
               a.margin_trade_tc_adj tc,
               a.open_bal_money_adj  openBalMoneyAdj,
               a.broker_branch_sum   brokerBranchSum
        
          from rzrq.tb_rzrq_cust_martc_cross a
         inner join xc.tb_h_snap_user b on a.user_id = b.user_id
                                       and a.trademonth = b.archive_date
                                       and b.user_type in ('0','1','3','4','5')
         where a.cust_branch <> a.broker_branch
        union all
        select c.trademonth          tradeMonth,
               c.broker_branch       brokerBranch,
               decode (d.user_type,'3','2','5','2',d.user_type )userType,
               c.normal_trade_tc_adj tc,
               0.0000                openBalMoneyAdj,
               c.broker_branch_sum   brokerBranchSum
               
          from rzrq.tb_rzrq_cust_nortc_cross c
         inner join xc.tb_h_snap_user d on c.user_id = d.user_id
                                       and c.trademonth = d.archive_date
                                       and d.user_type in ('0','1','3','4','5')
         where c.cust_branch <> c.broker_branch) t
 group by t.tradeMonth, t.brokerBranch, t.userType

分组使用group by ，根据t.tradeMonth（月份）、t.brokerBranch（营业部号）、t.userType（用户类型）分组

行转列使用case when then 实现