Repeating a SQL Row Based on a Value in a Different Column

There are times that we get requirements such as populating and duplicate SQL rows, based on a value, on another column. E.g.: In an inventory system, when items are received, the details will be saved in the following format (ItemDetails):

And we are asked to create a GUI for the end user to enter ‘Serial Numbers’ for each item. And we have to repeat the above mentioned item codes a number of times which equals to the ‘ItemQty’. Of course, we can achieve that using a SQL cursor or iterate using C# code. But in the following example, I will show how to do it using SQL.

The task would have been very simple if we would have another table with a structure similar to this (TempTable):

So when the two tables are joined, ‘ItemDetails’ will repeat according to the row count of ‘TempTable’. But it is not very practical, and it will result in duplicate data, which will grow your database unnecessarily when time goes.

Instead, we can use a single table which contains a series of numbers. These numbers will start from ‘1’. And the end should be the maximum quantity which an item can have. For this example, I will take ‘10’ as the maximum value. And that table should have the following structure.

Use the following T-SQL statement to create the table:

CREATE TABLE [IntermediateTable](
    [MaxQty] [int] NULL
) ON [PRIMARY]

For this example, I have inserted up to 20. But in a real world scenario, it may be required to enter values (more than 1000). In such a situation, you can use the following T-SQL statement to insert values.

insert into IntermediateTable
select 
    thousand.number*1000 + 
    hundred.number*100 + 
    ten.number*10 + 
    one.number
from(
    select 1 as number union select 2 union select 3 union select 4 union 
    select 5 union select 6 union select 7 union select 8 union 
    select 9 union select 0) one
cross join (select 1 as number union select 2 union select 3 union 
  select 4 union select 5 union select 6 union select 7 union 
  select 8 union select 9 union select 0) ten
cross join (select 1 as number union select 2 union select 3 union 
  select 4 union select 5 union select 6 union select 7 union 
  select 8 union select 9 union select 0) hundred
cross join (select 1 as number union select 2 union select 3 union select 4 
  union select 5 union select 6 union select 7 union select 8 
  union select 9 union select 0) thousand
where (thousand.number*1000 + hundred.number*100 + ten.number*10 + one.number) > 0 and 
      (thousand.number*1000 + hundred.number*100 + ten.number*10 + one.number) <= 2000
order by (thousand.number*1000 + hundred.number*100 + ten.number*10 + one.number)

** Please note: The above statement will insert values from ‘1’ to ‘2000’. But removing the where condition will insert values from ‘0’ to ‘10000’.

And using the following T-SQL statement, we can join the table and produce the required result.

select A.*
from ItemDetails as A
join IntermediateTable as B on B.MaxQty <= A.ItemQty
where A.BatchNo = 'B1'