Q
Problem solve Get help with specific problems with your technologies, process and projects.

Generate N rows for a column value of N

I have a subscriptions table for magazines, with fields like term (1 year, 2 years, etc.), quantity, subscriber name, amount, etc. I need to produce labels that will be placed on each copy to be delivered to subscribers. There are instances where the quantity is more than one, let's say 25, so I will need to produce 25 labels with the same subscriber name (e.g. John Stuart). Is there an SQL statement that can produce the records that I need?

Yes, by using a theta join between an integers table and your subscriptions table.

If you don't already have an integers table (it does come in handy for so many uses), create one like this:

 create table integers (i integer); insert into integers (i) values (0); insert into integers (i) values (1); insert into integers (i) values (2); ... and so on until you have enough values

The number of integers you need in the table must be as large as the largest term value in the Subscriptions table. Then your query is:

 select SubscriberName from integers inner join Subscriptions on i between 1 and term

How this works is that each row of the integers table will be matched against a row in the Subscriptions table, based on the integer value i being between 1 and the number in term. So if John Stuart's term is 25, then there will be 25 rows of the integers table that match this row. Hence the query will return the SubscriberName John Stuart 25 times.

This was last published in November 2004

Dig Deeper on SQL Server Database Modeling and Design

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close