15,563,053 members
See more:
```I have following quantity table

<pre>Item	QuantityRequired	MaxQuantity
Item1	      200	               50
Item2	      100	               30```

Based on MaxQuantity column value, table rows has to get split like below,

```Item	QuantityRequired
Item1	  50
Item1	  50
Item1	  50
Item1	  50
Item2	  30
Item2	  30
Item2	  30
Item2	  10```

What I have tried:

I treid with union its not working.
Posted
Updated 24-Apr-20 7:15am
CHill60 24-Apr-20 11:18am
Show the code you tried

## Solution 2

I chose to use a recursive CTE (Common Table Expression) to do this, combined with another CTE
SQL
```;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

v4
Member 14636607 25-Apr-20 3:50am
THANK YOU SIR

## Solution 3

Here's a solution with a single recursive CTE[^]:
SQL
```WITH cte As
(
SELECT
Item,
CASE
WHEN QuantityRequired > MaxQuantity THEN MaxQuantity
ELSE QuantityRequired
END As QuantityRequired,
QuantityRequired - MaxQuantity As RemainingQuantity,
MaxQuantity
FROM
YourTable

UNION ALL

SELECT
Item,
CASE
WHEN RemainingQuantity > MaxQuantity THEN MaxQuantity
ELSE RemainingQuantity
END As QuantityRequired,
RemainingQuantity - MaxQuantity As RemainingQuantity,
MaxQuantity
FROM
cte
WHERE
RemainingQuantity > 0
)
SELECT
Item,
QuantityRequired
FROM
cte
ORDER BY
Item
;```
Output:
```Item    QuantityRequired
------------------------
Item1	50
Item1	50
Item1	50
Item1	50

Item2	30
Item2	30
Item2	30
Item2	10```