Split row based on result of divide operation

Question

I have data shipment and multiply of 1 basket, Before :

Pack ID Brand   Part    Ship Qty    Qty per Basket  divideval   mod Batch
4       Brand A Part P  145         50              2           45  OB
4       Brand A Part P  125         50              2           25  OB2

I need to multiple the data based on ship qty / qty per basket, After :

Pack ID Brand   Part    Ship Qty    Batch
4       Brand A Part P  50          OB
4       Brand A Part P  50          OB
4       Brand A Part P  45          OB
4       Brand A Part P  50          OB2
4       Brand A Part P  50          OB2
4       Brand A Part P  25          OB2

How to make it using SQL Server?

Answer 1

Since you have already calculated the Qty per Basket, it makes thing a little bit simpler.

The solution use a number / tally table or recursive cte to generate one.

The following query uses a number table

select  t.PackID, t.Brand, t.Part, 
        case when n = 1 and [mod] <> 0 then [mod] else t.QtyperBasket end as ShipQty, 
        t.Batch
from    yourtable t
        cross join numbers n
where   n.n >= 1
and     n.n <= t.divideval + case when [mod] > 0 then 1 else 0 end