Selecting the same row multiple times

I have a table that has some children of a master object. Any child can occur more than once, and there is a Occurences column that contains that number, so the data in the table is something like:

ChildID | ParentID | Occurences
-------------------------------
      1 |        1 |        2
      2 |        1 |        2
      3 |        2 |        1
      4 |        2 |        3

I need to get a list of all the children, with each child appearing the corect number of times in the result, something like

  • Global connection already exists. Call sql.close() first
  • Bulk Import XML into SQL Server
  • Error querying “Microsoft.ACE.OLEDB.12.0” provider from SQL Server
  • SQL Server query to select records for a specific foreign key/month combination, or the master record if one does not exist for that day
  • SQL XML load with OpenRow
  • Data changes in a CTE after SELECT query
  • IDENT | ChildID | ParentID
    --------------------------
        1 |       1 |        1
        2 |       1 |        1
        3 |       2 |        1
        4 |       2 |        1
        5 |       3 |        2
        6 |       4 |        2
        7 |       4 |        2
        8 |       4 |        2
    

    I can do this with a cursor that loops the table and inserts as many rows as neccessary, but I don’t think that that is the best solution possible.

    Thanks for the help


    Create script included:

    DECLARE @Children TABLE (ChildID int, ParentID int, Occurences int)
    
    INSERT  @Children
    SELECT  1, 1, 2 UNION ALL
    SELECT  2, 1, 2 UNION ALL
    SELECT  3, 2, 1 UNION ALL
    SELECT  4, 2, 3
    

    2 Solutions collect form web for “Selecting the same row multiple times”

    ;with C as
    (
      select ChildID,
             ParentID,
             Occurences - 1 as Occurences
      from @Children
      union all
      select ChildID,
             ParentID,
             Occurences - 1 as Occurences
      from C
      where Occurences > 0
    )
    select row_number() over(order by ChildID) as IDENT,
           ChildID,
           ParentID
    from C
    order by IDENT
    
    ;WITH CTEs
    AS
    (
        SELECT 1 [Id]
        UNION ALL
        SELECT [Id] + 1 FROM CTEs WHERE [Id] < 100
    )
    SELECT ROW_NUMBER() OVER(ORDER BY c1.ChildID, c1.ParentID) [rn]
        , c1.ChildID, c1.ParentID
    FROM CTEs ct
    JOIN @Children c1 ON c1.Occurences >= ct.[Id]
    

    Another way to generate sequence is using predefined table, e.g. master.dbo.spt_values:

    SELECT ROW_NUMBER() OVER(ORDER BY c1.ChildID, c1.ParentID) [rn]
        , c1.ChildID, c1.ParentID
    FROM master.dbo.spt_values ct
    JOIN @Children c1 ON c1.Occurences > ct.number
        AND ct.type = 'P'
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.