如何将Sql Int值拆分为多行

Lets say I have the following table in MS SQL 2000

可以说我在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?

任何想法如何实现这一目标?

2 个解决方案

#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

#2

DECLARE @Id INT
DECLARE @Description VARCHAR(32)
DECLARE @Quantity INT
DECLARE @Results TABLE (Id INT, [description] VARCHAR(32), quantity INT)
DECLARE MyCursor CURSOR FOR
    SELECT Id, [description], quantity
    FROM
        MyTable

OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @Id, @Description, @Quantity

WHILE @@FETCH_STATUS = 0
BEGIN
    WHILE @Quantity > 0
    BEGIN
        INSERT INTO @Results (
            Id,
            [description],
            quantity
        ) VALUES (
            @Id,
            @Description,
            1
        )
        SET @Quantity = @Quantity - 1
    END
    FETCH NEXT FROM MyCursor INTO @Id, @Description, @Quantity
END

CLOSE MyCursor
DEALLOCATE MyCursor

SELECT *
FROM
    @Results

By the way, cursors are generally considered evil. So I will both recommend against something like this, and thank everyone in advance for their flames ;) (But it should work)

顺便说一句,游标通常被认为是邪恶的。所以我会建议反对这样的事情,并提前感谢所有人的火焰;)(但它应该工作)

#1