I chose to use a recursive CTE (Common Table Expression) to do this, combined with another CTE
;with cte1 as
(
select item, quantityrequired, maxquantity,
cast(QuantityRequired / maxquantity as integer) as wholes, QuantityRequired % MaxQuantity as partials
from #test
)
,cte2 as
(
SELECT item, maxquantity, wholes, 'base ' as w from cte1
UNION ALL
SELECT item, maxquantity, wholes - 1, 'recur' as w
FROM cte2 WHERE wholes > 1
)
SELECT item, MaxQuantity as QuantityRequired
from cte2
union all
SELECT item, partials as QuantityRequired
from cte1 where partials > 0
order by Item, QuantityRequired desc
Edit - meant to explain this before I submitted it!
The first CTE is determining how many rows I need,
wholes
is the number of rows where I can use the maximum quantity allowed (required / max),
partials
is what is left over (required mod max)
The 2nd CTE generates all of the 'whole' rows needed - I included that column w to help you see what was happening (if you want to include it in the select) - a full explanation of recursive CTEs can be found at
Recursive CTEs Explained - Essential SQL[
^]
The final select takes all of those "whole" rows and unions with the left-over "partial" value for each item - if there is one. Note the use of Union
all so the duplicate "whole" rows are not removed