Binaryworld - How to generate duplicates of existing rows based on the count value i ... [ SQL Server -> T-SQL ]

create table #temp (
rowid int primary key, rowname char(10), repeat_times int
);
insert into #temp values (1,'aaa',1);
insert into #temp values (2,'bbb',5);
insert into #temp values (3,'ccc',1);
insert into #temp values (4,'ddd',2);
select * from #temp;
/*
rowid    rowname  repeat_times
----------- ---------- ------------
     1 aaa          1
     2 bbb          5
     3 ccc          1
     4 ddd          2
*/
-- Objective:
-- To generate duplicates of each row based on the value
-- in repeat_times column. This example assumes that the
-- value is less than or equal to 5 only.
SELECT t1.*
FROM #temp AS t1
JOIN (
SELECT 1 AS cnt
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
) AS t2
ON t2.cnt <= t1.repeat_times
ORDER BY t1.rowid , t1.rowname , t1.repeat_times;
/*
rowid    rowname  repeat_times
----------- ---------- ------------
     1 aaa          1
     2 bbb          5
     2 bbb          5
     2 bbb          5
     2 bbb          5
     2 bbb          5
     3 ccc          1
     4 ddd          2
     4 ddd          2
*/

-- Generic solution using a Numbers table
SELECT t1.*
FROM #temp AS t1
JOIN Numbers AS t2
ON t2.Number > 0 And t2.Number <= t1.repeat_times
ORDER BY t1.rowid , t1.rowname , t1.repeat_times;