Selecting the same row multiple times

Question

I have a table with several children. Any child can meet several times, and there is a column “Events” that contains this number, so the data in the table looks something like this:

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, each of which displays the number of times as a result, something like

 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 processes the table and inserts as many rows as necessary, but I don't think this is the best solution.

thanks for the help

---

Create the script bundled:

 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 

Answer 1

 ;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 

Answer 2

 ;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 a sequence is to use a predefined table, for example. 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'