Selecting Rows Multiple Times Based on Quantity Column

Databases
1

I am working with an existing database with a Sales table with a structure similar to the following:

ID | Quantity | Product
======================
1 | 2 | Widget
2 | 3 | Fancy Widget

I need to be able to return multiple instances of each row based on the quantity of products sold, so the result I need is:

ID | Product
======================
1 | Widget
1 | Widget
2 | Fancy Widget
2 | Fancy Widget
2 | Fancy Widget

I am using SQL Server 2005 and would be grateful for any assistance in achieving this

Thanks

Sean :slight_smile:

2

I was wondering how long it would take you to reply! Good to see you’re still here.

Yes, I do want to return hundreds of thousand copies of the same row if that is what was sold. I need to take the data from the sales system and insert it into a customer support system. If a quantity of 10 products was sold then there should be 10 individual items in the support system that will then have further date populated from other sources, such as serial numbers, location data, etc. The same applies if quantity is 2 or 10,000 (probably a more realistic maximum at the moment). The company only cares about quantity at the sales level, but support has to know every single item

Sean :slight_smile:

3

you actually want to generate hundreds of thousands of copies of the same row?

that’s some database integration, there, lady

4

Thank you for the suggestions swampBoogie. Unfortunately, the value of quantity could be in the thousands or even the hundreds of thousands which makes it difficult

I am integrating various systems at the database layer. There is no server-side language involved unfortunately

Sean :slight_smile:

5

Create a table with one column containing integer values from 1 to the highest value of
quantity.


select ID,
       Product
  from numbers
  join sales
    on numbers.q <= sales.quantity

If the highest value of quantity is small you can do it on the fly, e.g. if highest quantity value is 4


select ID,
       Product
  from (select 1 as q
         union all
        select 2
         union all
        select 3
         union all
        select 4) dt
  join sales
    on dt.q <= sales.quantity
6

What is the server-side language in use?

7

you can try somthing like


declare @T as table
(
ID int,
Quantity int,
Product char(50) null
)

declare @TOUT as table
(
ID int,
Product char(50) null
)

insert into @T values(1,2,'Widget')
insert into @T values(2,3,'Fnacy Widget')

select * from @T


Declare @nextID int
Declare @quantity int
Declare @I int

Select @nextID=0


While 1=1
begin

Select @NextID=(select min(ID) from @T where ID>@nextId)
Select @quantity=(select min(Quantity) from @T where ID=@nextId)

if @nextId is null
break
else
set @I=0
while @I<@quantity
	begin
	insert into @TOUT(ID,Product) 
	select ID,Product from @T where ID=@nextID
		set @I=@I +1	
	end
	
continue

end

select *
from @TOUT
8

what particular information/s about the item does the support wanted to know?