CODEWITHSUNDEEP
sqlsql-server
user3179585
user3179585

Reputation: 123

SQL Split One Row To Multiple Rows Based on Column Number

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

Upvotes: 2

Views: 3882

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 82010

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

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1271141

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).

Upvotes: -1

Related Questions