CODEWITHSUNDEEP
sqlsql-servert-sql
SWeko
SWeko

Reputation: 30882

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

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

Upvotes: 8

Views: 3504

Answers (2)

Kirill Polishchuk
Kirill Polishchuk

Reputation: 56162

;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'

Upvotes: 4

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

;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

Upvotes: 9

Related Questions