8

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

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
Kirill Polishchuk
  • 54,804
  • 11
  • 122
  • 125
SWeko
  • 30,434
  • 10
  • 71
  • 106
  • 1
    Do you mind if I ask you why you need to do this? Maybe there's a better way than selecting the same row several times. – EdoDodo Jul 07 '11 at 08:58
  • I need to generate a row for each child, because there are some extra data that can be different. – SWeko Jul 07 '11 at 09:23

2 Answers2

9
;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
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • One more thing, in my case the Occurences number is small (10 tops) but if the number is in the hundreds, this will exceed the recursion limit. – SWeko Jul 07 '11 at 09:21
  • @SWeko – You can take care of that by specifying `OPTION (MAXRECURSION 0)`. – Mikael Eriksson Jul 07 '11 at 09:29
4
;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'
Kirill Polishchuk
  • 54,804
  • 11
  • 122
  • 125
  • Cool, this basically joins the source table with a numbers table, with a nice join condition. – SWeko Jul 07 '11 at 09:18
  • In my case the Occurences number is small (10 tops) but if the number is in the hundreds, this will exceed the recursion limit. That could be sidestepped if the numbers table is pre-generated. – SWeko Jul 07 '11 at 09:22
  • or sidestepped with "option( MAXRECURSION 0)" – t-clausen.dk Jul 07 '11 at 14:18