Oracle行转列 pivot函数 常见用法

1.根据分类

select

    code,

    proname,

    amt_jbgz,

    ...

from bgt_extable

pivot ( sum(nvl(金额,0)) for 分类字段 in(

    0 as amt_jbgz,--基本工资

    1 as amt_jj,--奖金

    ...

))

2.添加后缀（多个行转列）

select

    code,

    proname,

    amt_jbgz_shb,

    amt_jbgz_shh,

    amt_jbgz_yss,

    ...

from bgt_extable

pivot ( sum(nvl(je_shb,0)) as shb,sum(nvl(je_shh,0)) as shh,sum(nvl(je_yss)) as yss for 分类字段 in(

    0 as amt_jbgz,--基本工资

    1 as amt_jj,--奖金

    ...

))

3.单一多条件

select

    code,

    proname,

    amt_jbgz_shb,

    amt_jbgz_shh,

    amt_jbgz_yss,

    amt_qtsr_shb,

    amt_qtsr_shh,

    amt_qtsr_yss

    ...

from bgt_extable

pivot ( sum(nvl(je_shb,0)) as shb,sum(nvl(je_shh,0)) as shh,sum(nvl(je_yss)) as yss for 分类字段 in(

    0 as amt_jbgz,--基本工资

    1 as amt_jj,--奖金

    3,4,5,99 as amt_qtsr--其他收入

    ...

))

4.非单一复杂条件

这个用的是最原始的 case when/group by

select
dwid,
sfbn,
sum(case when zjlx=35 and sfbn = 1 then amt_annual_shb else 0 end) amt_bn_jfbk_shb,--经费拨款
sum(case when zjlx=35 and sfbn = 1 then amt_annual_shh else 0 end) amt_bn_jfbk_shh,
sum(case when zjlx=35 and sfbn = 1 then amt_annual_yss else 0 end) amt_bn_jfbk_yss

...

from basedata
group by dwid,sfbn