APPS-ORACLE.RU

with t as
(
  select 'a' a, 2 n from dual union all
  select 'b' a, 3 n from dual union all
  select 'c' a, 5 n from dual union all
  select 'd' a, 1 n from dual union all
  select 'e' a, 0 n from dual
)
select *
 from t,
      table(select collect(level) from dual connect by level<=t.n);

A          N COLUMN_VALUE
- ---------- ------------
a          2            1
a          2            2
b          3            1
b          3            2
b          3            3
c          5            1
c          5            2
c          5            3
c          5            4
c          5            5
d          1            1
e          0            1

12 rows selected

with t as
(
  select 1 my_type, 'a' val from dual
   union all
  select 1 my_type, 'b' val from dual
   union all
  select 3 my_type, 'd' val from dual
   union all
  select 2 my_type, 'l' val from dual
   union all
  select 2 my_type, 'w' val from dual
   union all
  select 2 my_type, 't' val from dual
)
------------------------------------------
select my_type,
       case when column_value > 1 then null
            else val
       end val
from (
      -- получаем количество строк, которое необходимо добавить
      select my_type,
             val,
             rn,
             max_rn,
             case when my_type = next_type then 1
                  else max_rn - rn + 1
             end add_rn,
             next_type
      from (
            -- получаем max количество строк и тип строки идущий следующим
            select my_type,
                   val,
                   rn,
                   max(rn) over()          as max_rn,
                   lead(my_type,1,0) over(order by my_type) next_type
            from (
                  -- получаем количество строк для каждого типа
                  select my_type,
                         val,
                         count(1) over(partition by my_type) as rn
                  from t
                 )
            )
      ) d
      ,table(select collect(level) from dual connect by level<=d.add_rn)

   MY_TYPE VAL
---------- ---
         1 a
         1 b
         1
         2 t
         2 l
         2 w
         3 d
         3
         3

9 rows selected