I have an order table and I will keep it simple. I need to split the rows based on the quantity in the order. For example, if an order quantity is 4 I need to split the original rows into 4 rows with quantity of 1 each. Example data below.
ID FKID Product QTY
1 100 Widget 4
I need a result like this.
ID FKID Product QTY
1 100 Widget 1
2 100 Widget 1
3 100 Widget 1
4 100 Widget 1
Just another option using an ad-hoc tally table and a simple JOIN
Example
Select ID = row_number() over (partition by A.ID order by N)
,FKID
,Product
,Qty = 1
From YourTable A
Join (
Select Top (1000) N=Row_Number() Over (Order By (Select NULL))
From master..spt_values n1 ,master..spt_values n2
) B on N<=A.[QTY]
Returns
ID FKID Product Qty
1 100 Widget 1
2 100 Widget 1
3 100 Widget 1
4 100 Widget 1
One simple method is a recursive CTE:
with cte as (
select ID, FKID, Product, QTY
from t
union all
select ID, FKID, Product, QTY - 1
from t
where qty > 1
)
select id, fkid, product, 1 as qty
from cte;
The only caveat is that if qty can be 100 or greater, you'll need option (maxrecursion 0).
QTYgo?