How to repeat a record n times - SQL Server

Question

I'm querying webdata that returns a list of items and the quantity owned. I need to translate that into multiple records - one for each item owned. For example, I might see this result: {"part_id": 118,"quantity": 3}. But in my database I need to be able to interact with each item individually, to assign them locations, properties, etc.

It would look like this:

Part_ID     CopyNum
-------------------
118         1
118         2
118         3

In the past, I've kept a table I called [Count] that was just a list of integers from 1 to 100 and I did a cross join with the condition that Count.Num <= Qty

I'd like to do this without the Count table, which seems like a hack. How can I do this on the fly?

I don't quite follow what you're asking here. Seems like you just want a tally to duplicate the rows. If so, does this answer your question? [Repeat Rows N Times According to Column Value](https://stackoverflow.com/q/33327837/2029983) (A search of your question's title would have very likely given you that topic, as it did for me). If not, why not? What does your data look like, and what are your expected results? — Thom A, Jun 17 '20 at 16:45
I don't think I'd consider that a hack at all. Keeping a number table around for this kind of thing is pretty common — superstator, Jun 17 '20 at 16:45

score 2 · Answer 1 · answered Jun 17 '20 at 17:13

If you don't have a tally/numbers table (highly recommended), you can use an ad-hoc tally table in concert with a CROSS APPLY

Example

Declare @YourTable Table ([Part_ID] int,[Quantity] int)  Insert Into @YourTable Values 
 (118,3)
,(125,2)

Select A.Part_ID
      ,CopyNum = B.N 
 From @YourTable A
 Cross Apply ( Select Top (Quantity) N=Row_Number() Over (Order By (Select NULL)) 
               From  master..spt_values n1, master..spt_values n2
            ) B

Returns

Part_ID CopyNum
118     1
118     2
118     3
125     1
125     2

How to repeat a record n times - SQL Server

1 Answers1