Create a sequence from the starting and ending values in a SQL Server 2008 table?

Question

I am using SQL Server 2008. I have a table temp:

select * from temp

returns this:

name    start_limit   end_limit
-------------------------------
j       2             7
t       1             9

Now I want a to generate the following sequence

name    allowed values
----------------------
j       2
j       3
j       4
j       5
j       6
j       7
t       1
t       2
t       3
t       4
t       5
t       6
t       7
t       8
t       9

What should I do?

This will probably help http://stackoverflow.com/questions/21425546/how-to-generate-a-range-of-numbers-between-two-numbers-in-sql-server — DavidG, Feb 01 '17 at 10:20

score 3 · Answer 1 · answered Feb 01 '17 at 10:22

This will work perfectly:

;with a as(
select name,start_limit as [allowed values] from temp
union all
select b.name,a.[allowed values]+1 as [allowed values] from temp b inner join a on b.name=a.name and b.end_limit>a.[allowed values]
)
select * from a order by name,seq;

score 3 · Answer 2 · answered Feb 01 '17 at 10:26

Here is one way using tally table no recursion required

;WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4)
SELECT a.name,c.N
FROM   ctetally c
       JOIN Yourtable a
         ON c.N BETWEEN a.start_limit AND a.end_limit

Create a sequence from the starting and ending values in a SQL Server 2008 table?

2 Answers2