Oracle 21c

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

create table tbl (item varchar(20), itemQty int);
insert all into tbl values('item1', 4)
select 1 from dual;
1 rows affected

insert all into tbl values('item2', 2)
select 1 from dual;
1 rows affected

select * from tbl;


ITEM
ITEMQTY


item1
4

item2
2


SELECT T.ITEM, C.RN itemQty 
  FROM tbl T JOIN
  (
    SELECT  ROWNUM AS RN
    FROM dual
    CONNECT BY 
    ROWNUM <= (Select MAX(itemQty) From tbl)
  ) C
ON T.ITEMQTY >= C.RN
ORDER BY T.ITEM, C.RN


ITEM
ITEMQTY


item1
1

item1
2

item1
3

item1
4

item2
1

item2
2


WITH CTE(item,itemQty, qty) AS
(
  SELECT item, itemQty, 1 FROM tbl
  UNION ALL 
  SELECT item,itemQty, qty+1 
  FROM CTE
  WHERE qty+1 <= itemQty
)

SELECT ITEM, QTY FROM CTE
ORDER BY ITEM, QTY


ITEM
QTY


item1
1

item1
2

item1
3

item1
4

item2
1

item2
2