但行好事
莫论前程❤

Oracle学习总结—-行转列decode、case when、pivot

img

img

decode

select ep.name 计划名称,
       ep.code 计划编号,
       ec.name 企业名称,
       ec.corp_no 企业编号,
       ee.name 个人姓名,
       ee.identity_no 证件号码,
       ei.identity_name 证件类型,
       sum(decode(ea.code, 'PPT000', t.cash_balance, 0)) 员工个人存量税后账户金额,
       sum(decode(ea.code, 'PPT000', t.balance, 0)) 员工个人存量税后账户份额,
       sum(decode(ea.code, 'PPG000', t.cash_balance, 0)) 员工个人存量税前账户金额,
       sum(decode(ea.code, 'PPG000', t.balance, 0)) 员工个人存量税前账户份额,
       sum(decode(ea.code, 'PPA000', t.cash_balance, 0)) 员工个人存量税优账户份额,
       sum(decode(ea.code, 'PPA000', t.balance, 0)) 员工个人存量税优账户份额,
       sum(decode(ea.code, 'PCT000', t.cash_balance, 0)) 员工企业存量税后账户金额,
       sum(decode(ea.code, 'PCT000', t.balance, 0)) 员工企业存量税后账户份额,
       sum(decode(ea.code, 'PCG000', t.cash_balance, 0)) 员工企业存量税前账户金额,
       sum(decode(ea.code, 'PCG000', t.balance, 0)) 员工企业存量税前账户份额,
       sum(decode(ea.code, 'PCA000', t.cash_balance, 0)) 员工企业存量税优账户金额,
       sum(decode(ea.code, 'PCA000', t.balance, 0)) 员工企业存量税优账户份额
  from eprk_person_taxed_account t,
       eprk_plan                 ep,
       eprk_corporation          ec,
       eprk_accounttype          ea,
       eprk_person               ee,
       eprk_identity_type        ei
 where 1=1
   and t.pk_plan = ep.pk_plan
   and t.pk_corporation = ec.pk_corporation
   and t.pk_accounttype = ea.pk_accounttype
   and ee.pk_person = t.pk_person
   and ei.pk_identity_type = ee.pk_identity_type
   and t.pk_fund <> '00000000000000000000'
   and rownum<=100
 group by ep.name,ec.name,ee.name,ep.code,ec.corp_no,ee.identity_no,ei.identity_name

case when

select ep.name 计划名称,
       ep.code 计划编号,
       ec.name 企业名称,
       ec.corp_no 企业编号,
       ee.name 个人姓名,
       ee.identity_no 证件号码,
       ei.identity_name 证件类型,
       sum(case when ea.code = 'PPT000' then t.cash_balance else 0 end) 员工个人存量税后账户金额,
       sum(case when ea.code = 'PPT000' then t.balance else 0 end) 员工个人存量税后账户份额,
       sum(case when ea.code = 'PPG000' then t.cash_balance else 0 end) 员工个人存量税前账户金额,
       sum(case when ea.code = 'PPG000' then t.balance else 0 end) 员工个人存量税前账户份额,
       sum(case when ea.code = 'PPA000' then t.cash_balance else 0 end) 员工个人存量税优账户份额,
       sum(case when ea.code = 'PPA000' then t.balance else 0 end) 员工个人存量税优账户份额,
       sum(case when ea.code = 'PCT000' then t.cash_balance else 0 end)  员工企业存量税后账户金额,
       sum(case when ea.code = 'PCT000' then t.balance else 0 end)  员工企业存量税后账户份额,
       sum(case when ea.code = 'PCG000' then t.cash_balance else 0 end) 员工企业存量税前账户金额,
       sum(case when ea.code = 'PCG000' then t.balance else 0 end) 员工企业存量税前账户份额,
       sum(case when ea.code = 'PCA000' then t.cash_balance else 0 end)  员工企业存量税优账户金额,
       sum(case when ea.code = 'PCA000' then t.balance else 0 end) 员工企业存量税优账户份额
  from eprk_person_taxed_account t,
       eprk_plan                 ep,
       eprk_corporation          ec,
       eprk_accounttype          ea,
       eprk_person               ee,
       eprk_identity_type        ei
 where 1=1
   and t.pk_plan = ep.pk_plan
   and t.pk_corporation = ec.pk_corporation
   and t.pk_accounttype = ea.pk_accounttype
   and ee.pk_person = t.pk_person
   and ei.pk_identity_type = ee.pk_identity_type
   and t.pk_fund <> '00000000000000000000'
   and rownum<=100
 group by ep.name,ec.name,ee.name,ep.code,ec.corp_no,ee.identity_no,ei.identity_name

pivot 函数 oracle 11g

select *
  from (select ep.name          计划名称,
               ep.code          计划编号,
               ec.name          企业名称,
               ec.corp_no       企业编号,
               ee.name          个人姓名,
               ee.identity_no   证件号码,
               ei.identity_name 证件类型,
               ea.code          accounttype_code,
               t.cash_balance,
               t.balance
          from eprk_person_taxed_account t,
               eprk_plan                 ep,
               eprk_corporation          ec,
               eprk_accounttype          ea,
               eprk_person               ee,
               eprk_identity_type        ei
         where 1 = 1
           and t.pk_plan = ep.pk_plan
           and t.pk_corporation = ec.pk_corporation
           and t.pk_accounttype = ea.pk_accounttype
           and ee.pk_person = t.pk_person
           and ei.pk_identity_type = ee.pk_identity_type
           and t.pk_fund <> '00000000000000000000'
           and rownum <= 100)
pivot(sum(cash_balance) as 金额, sum(balance) as 份额
   for accounttype_code in('PPT000' as 员工个人存量税后账户,
                           'PPG000' as 员工个人存量税前账户,
                           'PPA000' as 员工个人存量税优账户,
                           'PCT000' as 员工企业存量税后账户,
                           'PCG000' as 员工企业存量税前账户,
                           'PCA000' as 员工企业存量税优账户))
赞(2) 打赏
未经允许不得转载:刘鹏博客 » Oracle学习总结—-行转列decode、case when、pivot
分享到: 更多 (0)

评论 抢沙发

评论前必须登录!

 

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏