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;

This page was last updated on May 01, 2006 04:28 PM.