Use value in other column in loop while-CodePudding

I have this query in SQL Server and I have a table Rezotel with column name Gun int type.

I want to replace the number 100 in 'WHILE @i - 100 <= 0' by the value in Gun column
Edit -The point is I want it to iterate over the same row N number of times where N equals the Gun value of that row, so duplicate the insert of each row N number of times

DECLARE @TEST table (RecID int, D1 DATE)
DECLARE @i int = 0;
    WHILE @i - 100 <= 0
    BEGIN
        SET @i = @i 1
        INSERT INTO @TEST
        SELECT RezOtel.RecID,RezOtel.CikisTarihi-@i AS D1 FROM RezOtel
    END

CodePudding user response：

Based on the comments it appears something like the following might be what you're after (untested of course).

Using either a tally table (table of numbers) or a number series generated on the fly like below (SQLServer 2022 will support GenerateSeries()), join this to your source table and limit the rows based on the row's value of Gun

with 
 l0 as (select 0 v from (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))v(v)), 
 l1 as (select 0 v from l0 a cross join l0 b),
 nums as (select n = Row_Number() over(order by @@Spid) from l1)

insert into @TEST (RecID, D1)
select r.RecID, DateAdd(day, -n, r.CikisTarihi) as D1 
from RezOtel r
join nums n on n.n <= r.Gun;