Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Repeating a SQL row based on a value in a different column

, 20 Oct 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
How to repeat 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):

screen_01_thumb_2_.jpg

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

screen_01_thumb_2_.jpg

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.

screen_03_thumb.png

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'

screen_03_thumb.png

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Manjuke Fernando
Technical Lead Air Liquide Industrial Services (Singapore)
Singapore Singapore
I have been in software industry for more than 8 years. I have developed different type of software using different languages. Many of them are database related (both web & window based), SQL being as the back end most of the time. Up-to-date I have knowledge in languages such as C#, VB.Net, T-SQL, JAVA, VB6 & C++, making C# the most proficient of all. Also I have worked using different technologies like ASP.Net, SharePoint, Crystal Reports (But I really hate designing reports) & MS SQL Server and have involved in designing & developing software for major companies like FedEx, Softlogic Holdings, IronOne Technologies & Brandix. Currently I am working as a Tech Lead in Singapore.
Follow on   Twitter   LinkedIn

Comments and Discussions

 
QuestionWorks Pinmemberibuksh5-Jun-12 16:34 
GeneralMy vote of 2 Pinmemberdaveynogoodboyo24-Oct-11 13:41 
SuggestionAn auxilary table of numbers Pinmemberdaveynogoodboyo24-Oct-11 13:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 20 Oct 2011
Article Copyright 2011 by Manjuke Fernando
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid