Generating number between these start and end fields

Question

Hi there

I have 3 column (ID, Start and End) in TSQL.

ID; Start; END
1; 1; 5;
2; 10; 15;

So it will generate number like:

1, 1
1, 2
1, 3
1, 4
1, 5
2, 10
2, 11
2, 12
2, 13
2, 14
2, 15

Again all I can think of is cursor but is there any better way?

Answer 1

Something like this (untested)

;WITH cNumbers AS
(
   SELECT
       ROW_NUMBER() OVER (ORDER BY c1.object_id) AS ArbitraryNumber
   FROM
       sys.columns c1 CROSS JOIN sys.columns c2
)
SELECT
    M.ID, N.ArbritraryNumber
FROM
    MyTable M
    JOIN
    cNumbers N ON N.ArbitraryNumber BETWEEN M.Start AND M.End

Edit:

The cross join gives 298,116 rows in my tempdb. And 1,865,956 in a user DB. If nearly 300k isn't enough, add another CROSS JOIN.

This gives me 162,771,336 in tempdb and 2,548,895,896 in a user DB:

SELECT
    COUNT_BIG(*) --note bigint
FROM
    sys.columns c1 CROSS JOIN sys.columns c2 CROSS JOIN sys.columns c3

Answer 2

A CTE approach is easy

with tab as(
select 1 as id, 1 as start, 5 as en
union all
select 2, 10, 15),
 cte as(
select id,start,en from tab
union all
select id,start+1 , en from cte where start+1<=en)
select id,start from cte
order by id