Oracle数据透视行到列模式匹配

Question

我希望将行重新排列为列(在下面的tbl2中)，以计数EXEN的出现次数，以及MPACODE列以MPA开头的任何代码。

SELECT *
FROM   (select code from tbl2 where pidm='4062161')
PIVOT  (count(*) FOR (code) IN ('EXEN' AS EXEN, 'MPA%' AS MPACODE));

tbl2：

​

​

期望产出：

​

​

实际产出：

​

​

Answer 1

必须执行中间步骤才能将所有MPA%转换为MPA(参见子查询dt2 )。

with dt as (
select 'EXEN' code from dual union all
select 'MPA'||rownum from dual connect by level <= 10),
dt2 as (
select 
case when code like 'MPA%' then 'MPA' else code end as code
from dt)
select *
from dt2
pivot (
count(*) for
(code) IN ('EXEN' AS EXEN, 'MPA' AS MPACODE));

      EXEN    MPACODE
---------- ----------
         1         10

PIVOT执行相同的比较(不是LIKE)，因此这是无效的：'MPA%' AS MPACODE和查询失败的原因。

Answer 2

例如：

  select
   count(case when code='EXEN' then 1 end) exen,
   count(case when code like 'MPA%' then 1 end) mpacode
     from  tbl2 where pidm='4062161';