Oracle Pivot rows to columns pattern matching-CodePudding

I want to rearrange the rows to columns (in tbl2 below) to count the number of occurrences of EXEN for the EXEN col, and any code starting with MPA for the MPACODE column.

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

tbl2:

Desired output:

Actual output:

CodePudding user response：

You must perform an intermediate step to transform all MPA%to MPAsee subquery 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 perform an equal comparison (not LIKE), so this is not valid: 'MPA%' AS MPACODE and the reason why the query fails.

CodePudding user response：

for example:

  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';