SQL Server 2017

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

2 rows affected

WITH sheets AS (
  SELECT JobNo,
         Batch,
         Tag,
         UnitQtyInTag,
         UnitQtyInSheet,
         SheetQtyInTag,
         1 AS SheetNumber,
         CASE WHEN UnitQtyInSheet > UnitQtyInTag THEN UnitQtyInTag
              ELSE UnitQtyInSheet 
         END AS pcsinSheet,
         CASE WHEN UnitQtyInSheet > UnitQtyInTag THEN 0
              ELSE UnitQtyInTag - UnitQtyInSheet 
         END AS balance
  FROM MasterData
  UNION ALL
  SELECT JobNo,
         Batch,
         Tag,
         UnitQtyInTag,
         UnitQtyInSheet,
         SheetQtyInTag,
         SheetNumber + 1,
         CASE WHEN UnitQtyInSheet > balance THEN balance
              ELSE UnitQtyInSheet 
         END,
         CASE WHEN UnitQtyInSheet > balance THEN 0
              ELSE balance - UnitQtyInSheet 
         END AS balance
  FROM sheets
  WHERE balance > 0
)
SELECT JobNo, Batch, Tag, UnitQtyInTag, UnitQtyInSheet, SheetQtyInTag, SheetNumber, pcsinSheet
FROM sheets
ORDER BY JobNo, Batch, Tag, SheetNumber

JobNo	Batch	Tag	UnitQtyInTag	UnitQtyInSheet	SheetQtyInTag	SheetNumber	pcsinSheet
AAAA	B001	1	384	48	8	1	48
AAAA	B001	1	384	48	8	2	48
AAAA	B001	1	384	48	8	3	48
AAAA	B001	1	384	48	8	4	48
AAAA	B001	1	384	48	8	5	48
AAAA	B001	1	384	48	8	6	48
AAAA	B001	1	384	48	8	7	48
AAAA	B001	1	384	48	8	8	48
BBBB	B002	2	300	48	7	1	48
BBBB	B002	2	300	48	7	2	48
BBBB	B002	2	300	48	7	3	48
BBBB	B002	2	300	48	7	4	48
BBBB	B002	2	300	48	7	5	48
BBBB	B002	2	300	48	7	6	48
BBBB	B002	2	300	48	7	7	12