How to use column with value of N to turn 1 row into N number of rows

[adam0101]

I'm trying to export from a SQL Server database into a main frame. In the SQL Server database I have a table like this:

Quantity ProductName ModelNum
-------- ----------- --------
       3 CISCON      ATTW
       2 TRANCO      TRWS

What I'd like to do is use the Quantity field to duplicate a Product row that number of times. So my output would look like this:

ProductName ModelNum
----------- --------
CISCON      ATTW
CISCON      ATTW
CISCON      ATTW
TRANCO      TRWS
TRANCO      TRWS

How do I write a query to do this?

 

[rwheels]

What you need to do is create a cursor (loop), and another cursor inside of it.

Your outer cursor will grab each record. Place the values in variables. Then on the inner cursor, you need a variable (integer) that is incremented each pass. Inside of this inner cursor you will have the actual insert statemnt for the table you are creating. You break out of the inner cursor once your incremented variable = quantity (this will vary depending on semantics). I don't have the time to write the syntax, but that's the gist of what you need to do. Be careful about getting the numbers correct (i.e., beware of your logic of incrementing and reading the values. If your not careful, you will break out of the inner loop at the wrong time, and get inaccurate results.)

 

[adam0101]

Thanks, that's what I ended up doing and it works fine. I just wish there was a way to do it without a cursor. It slows my query down a lot.

 

[r937]

cursor not necessary

think in terms of sets, not procedures

use a theta join (sorry for the 50-cent word)

join your table to an integers table like this --

select ProductName, ModelNum
from integers
, yourtable
where i between 1 and Quantity

tada!! - every original row is joined to as many rows as there are integers between 1 and the Quantity on that row

the integer is not selected into the result set, but then, it wasn't supposed to

for another example of this use of the integers table, see thread701-553683


rudy

http://r937.com/