Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2008
    Posts
    1

    Unanswered: how to create resultSet with repeated record

    Hi,
    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

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    Try recursive SQL:

    WITH CTE ( ITERATION, COPIES , coumn1, column2 ... )
    AS
    ( SELECT 1 AS ITERATION , COPIES , coumn1, column2 ...
    FROM your-table
    UNION ALL
    SELECT ITERATION + 1 AS ITERATION , COPIES , coumn1, column2 ...
    FROM CTE WHERE COPIES > ITERATION )

    SELECT coumn1, column2 ... FROM CTE ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •