How to Split Sql Int Value into Multiple Rows

Question

Lets say I have the following table in MS SQL 2000

Id | description | quantity |
-------------------------------
1    my desc          3
2    desc 2           2

I need to display multiple rows based on the quantity, so I need the following output:

Id | description | quantity |
-----------------------------
1    my desc          1
1    my desc          1
1    my desc          1
2    desc 2           1
2    desc 2           1

Any ideas how to accomplish this?

Answer 1

This works just fine, no need for any cursors on this one. It may be possible to fangle something out without a number table as well.

Note if going for this kind of solution I would keep a number table around and not recreate it every time I ran the query.

create table #splitme (Id int, description varchar(255), quantity int) 
insert #splitme values (1    ,'my desc',          3)
insert #splitme values (2    ,'desc 2',           2)

create table #numbers (num int identity primary key) 
declare @i int 
select @i = max(quantity) from #splitme
while @i > 0
begin   
    insert #numbers default values 
    set @i = @i - 1 
end

select Id, description, 1 from #splitme 
join #numbers on num <= quantity