SQL Server 2008 : TSQL, select same data for different times based on column value -

i using mssql 2008 r2,

i have below structure

    create table #temp (     product int,     [order] int,     ord_qnty int     )      insert #temp      select 10 ,3,4 

now, if ord_qnty 4 , want select same product,order 4 times in 4 rows thevalue of ord_qnty should 1 , i.e.

out put should

    product order ord_qnty     10      3     1     10      3     1     10      3     1     10      3     1 

try 1 -

query:

declare @temp table  (       product int     , [order] int     , ord_qnty int ) insert @temp(product, [order], ord_qnty)  select 10, 3, 4  select         t.product     , t.[order]     , ord_qnty = 1  @temp t join [master].dbo.spt_values sv on t.ord_qnty > sv.number sv.[type] = 'p'  select         t.product     , t.[order]     , ord_qnty = 1  @temp t join (     select number = row_number() on (order (select 1))     sys.system_parameters p     ) sv on t.ord_qnty >= sv.number 

output:

product     order       ord_qnty ----------- ----------- ----------- 10          3           1 10          3           1 10          3           1 10          3           1 

query cost:

for "millions value":

set nocount on;  declare @numbers table (number int)  declare @temp table  (       product int     , [order] int     , ord_qnty int ) insert @temp(product, [order], ord_qnty)  select 10, 3, 4  declare        @i bigint = 1     , @max bigint = (             select max(ord_qnty)             @temp          )  while (@i <= @max) begin      insert @numbers (number)      values (@i), (@i+1), (@i+2), (@i+3), (@i+4), (@i+5), (@i+6), (@i+7), (@i+8), (@i+9)     select @i += 10  end  select        t.product     , t.[order]     , ord_qnty = 1  @temp t cross join (     select *      @numbers     number < @max + 1 ) t2