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