CODEWITHSUNDEEP
sql-servert-sql
rafastryk
rafastryk

Reputation: 13

How to split rows recursively

I've following table:

Id  CreationDate  FromEntryNo  ToEntryNo
 1   2013-01-01        1            4
 2   2013-01-03        5            8
 3   2013-01-05        9           11
...

I want to split this into multiple rows to have a list with all consecutive EntryNo, something like this:

Id  CreationDate  FromEntryNo  ToEntryNo   EntryNo
 1   2013-01-01        1            4         1
 1   2013-01-01        1            4         2
 1   2013-01-01        1            4         3
 1   2013-01-01        1            4         4
 2   2013-01-03        5            8         5
 2   2013-01-03        5            8         6
 2   2013-01-03        5            8         7
 2   2013-01-03        5            8         8
 3   2013-01-05        9           11         9
 3   2013-01-05        9           11        10
 3   2013-01-05        9           11        11
...

My first attempt is CTE with recursion, but it doesn't work:

with cte as
(select gr.Id, gr.CreationDate, gr.FromEntryNo, gr.ToEntryNo, gr.FromEntryNo as [EntryNo]
 from dbo.[Register] gr 
 union all
 select No, CreationDate, FromEntryNo, ToEntryNo, EntryNo + 1 from cte where EntryNo <= ToEntryNo
)
select Id, CreationDate, FromEntryNo, ToEntryNo, EntryNo from cte
<

Any idea how to do this using one SQL query?

Upvotes: 0

Views: 119

Answers (1)

Raj
Raj

Reputation: 10853

with cte as
(select gr.Id, gr.CreationDate, gr.FromEntryNo, gr.ToEntryNo, 
        gr.FromEntryNo as [EntryNo]
 from dbo.[Register] gr 
 union all
 select Id, CreationDate, FromEntryNo, 
        ToEntryNo, EntryNo + 1 
 from cte where EntryNo < ToEntryNo
)
select Id, CreationDate, FromEntryNo, ToEntryNo, EntryNo 
from cte
ORDER BY Id,EntryNo

Upvotes: 1

Related Questions