Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tablename (COL1 varchar2(1), COL2 int, COL3 varchar2(4));
INSERT ALL 
    INTO tablename (COL1, COL2, COL3)
         VALUES ('A', 1, 'VAL1')
    INTO tablename (COL1, COL2, COL3)
         VALUES ('A', 2, 'VAL2')
    INTO tablename (COL1, COL2, COL3)
         VALUES ('A', 4, 'VAL3')
    INTO tablename (COL1, COL2, COL3)
         VALUES ('B', 2, 'VAL4')
    INTO tablename (COL1, COL2, COL3)
         VALUES ('B', 4, 'VAL5')
    INTO tablename (COL1, COL2, COL3)
         VALUES ('B', 5, 'VAL6')
SELECT * FROM dual;
6 rows affected

WITH cte(COL1, COL2, max_col2) AS (
  SELECT COL1, MIN(COL2), MIN(COL2) + 2
  FROM tablename
  GROUP BY COL1
  UNION ALL
  SELECT COL1, COL2 + 1, max_col2
  FROM cte
  WHERE COL2 < max_col2
)
SELECT c.COL1, c.COL2, t.COL3
FROM cte c LEFT JOIN tablename t
ON t.COL1 = c.COL1 AND t.COL2 = c.COL2
ORDER BY c.COL1, c.COL2


COL1
COL2
COL3


A
1
VAL1

A
2
VAL2

A
3
null

B
2
VAL4

B
3
null

B
4
VAL5