Splitting a single row into multiple copies

J

j_racicot

I have a very weird situation that I'm hoping some of you might have
seen (and possibly resolved).

We're migrating data from one system to another and the business rules
have changed. System 1 allowed a user to create a record for an item
with a quantity > 1. System 2 doesn't permit a quantity > 1 for
certain items.

Is there a way to create multiple rows in the source table (or an
intermediate table) based on a list of item types which are not
allowed to have a quantity > 1?

Any help would be greatly appreciated.

Joel
 
M

Marshall Barton

I have a very weird situation that I'm hoping some of you might have
seen (and possibly resolved).

We're migrating data from one system to another and the business rules
have changed. System 1 allowed a user to create a record for an item
with a quantity > 1. System 2 doesn't permit a quantity > 1 for
certain items.

Is there a way to create multiple rows in the source table (or an
intermediate table) based on a list of item types which are not
allowed to have a quantity > 1?
Click to expand...


Create a table (named Numbers) with one field (named Num)
and populate it with records with values 1,2,3, ... up to
more than the greatest quantity for any item.

Then use a query something like this as the virtual
intermediate table to export:

SELECT Item, . . .
FROM sourcetable, Numbers
WHERE Num <= Quantity
 
Top