sql - Generating Rows Based on Column Value

Question

One of my tables in my database contains rows with requisition numbers and other related info. I am trying to create a second table (populated with an INSERT INTO statement) that duplicates these rows and adds a series value based on the value in the QuantityOrdered column.

For example, the first table is shown below:

+-------------+----------+
| Requisition | Quantity |
+-------------+----------+
| 10001_01_AD |    4     |
+-------------+----------+

and I would like the output to be as follows:

+-------------+----------+----------+
| Requisition | Quantity |  Series  |
+-------------+----------+----------+
| 10001_01_AD |     4    |     1    |
| 10001_01_AD |     4    |     2    |
| 10001_01_AD |     4    |     3    |
| 10001_01_AD |     4    |     4    |
+-------------+----------+----------+

I've been attempting to use Row_Number() to sequence the values but it's numbering rows based on instances of Requisition values, not based on the Quantity value.

Answer 1

You need recursive way :

with t as (
     select Requisition, 1 as start, Quantity
     from table
     union all
     select Requisition, start + 1, Quantity
     from t
     where start < Quantity
 ) 
select Requisition, Quantity, start as Series  
from t; 

However, by default it has limited to only 100 Quantities, if you have a more then you need to specify the query hint by using option (maxrecursion 0).