CODEWITHSUNDEEP
sqlsql-server
Steven F
Steven F

Reputation: 115

Generating Rows Based on Column Value

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.

Upvotes: 9

Views: 7773

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 176324

Non-recursive way:

SELECT *
FROM tab t
CROSS APPLY (SELECT n
             FROM (SELECT ROW_NUMBER() OVER(ORDER BY 1/0) AS n
                   FROM master..spt_values s1) AS sub
             WHERE  sub.n <= t.Quantity) AS s2(Series);

db<>fiddle demo

Upvotes: 6

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

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

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1271241

A simple method uses recursive CTEs:

with cte as (
      select requsition, quantity, 1 as series
      from t
      union all
      select requsition, quantity, 1 + series
      from t
      where lev < quantity
    )
select requsition, quantity, series
from cte;

With default setting, this works up to a quantity of 100. For larger quantities, you can add option (maxrecursion 0) to the query.

Upvotes: 2

Related Questions