Click here to Skip to main content
12,253,679 members (61,413 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

28.8K views
5 bookmarked
Posted

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
My passion lies in building business intelligence and data-based solutions, writing about things I work with and talking about it. New technologies relevant to my line of work interest me and I am often seen playing with early releases of such technologies.

My current role involves architecting and building a variety of data solutions, providing database maintenance and administration support, building the organization’s data practice, and training and mentoring peers.

My aspiration over the next several years is to achieve higher competency and recognition in the field of Data Analytics and move into a career of data science.


Specialities: SQL Server, T-SQL Development, SQL Server Administration, SSRS, SSIS, C#, ASP.Net, Crystal Reports

You may also be interested in...

Comments and Discussions

 
QuestionWorks Pin
ibuksh5-Jun-12 15:34
memberibuksh5-Jun-12 15:34 
GeneralMy vote of 2 Pin
daveynogoodboyo24-Oct-11 12:41
memberdaveynogoodboyo24-Oct-11 12:41 
SuggestionAn auxilary table of numbers Pin
daveynogoodboyo24-Oct-11 12:40
memberdaveynogoodboyo24-Oct-11 12:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    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.160426.1 | Last Updated 20 Oct 2011
Article Copyright 2011 by Manjuke Fernando
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid