Unanswered: how to create resultSet with repeated record
I have a table,it has bunch of columns,and also has a column named copies with type smallint,default to 1.I want use query to created a resultset that repeat those record in my resultSet,for example,if one record's copies field is 3,I need this record appears in my resultset 3 times,you can add extra columns in resultSet,such as random number.how to write this query,thanks.
any advance will be appreciated,thanks
WITH CTE ( ITERATION, COPIES , coumn1, column2 ... )
( SELECT 1 AS ITERATION , COPIES , coumn1, column2 ...
SELECT ITERATION + 1 AS ITERATION , COPIES , coumn1, column2 ...
FROM CTE WHERE COPIES > ITERATION )