Split the number range to individual number rows

Question

I have data like below

NumberFrom - NumberTo
1          - 2
3          - 3
5          - 7
...
...

I want to split them as individual rows like below

Number
1
2
3
5
6
7
.
.
.

Please suggest the way to do it.

Thank you

So in your example you have a single column that defines a range or is it two columns — SQLChao, Jan 31 '18 at 14:50
@LearnSQL I googled "sql server convert a range to individual rows" and found your answer in less than 10 seconds. When you are learning to program, learning how to google will be your most valuable tool/skill. — Tab Alleman, Jan 31 '18 at 14:52
please add DDL and insert script of yourtable. What version of sql are u using. — Pawan Kumar, Jan 31 '18 at 14:52

score 3 · Answer 1 · answered Jan 31 '18 at 14:53

3

I would do this using a recursive CTE:

with cte as (
      select numberfrom, numberto
      from t
      union all
      select numberfrom + 1, numberto
      from cte
      where numberfrom < numberto
     )
select numberfrom as number
from cte;

By default, this is limited to ranges of 100, but you can use the maxrecursion option to support larger ranges if needed.

answered Jan 31 '18 at 14:53

Gordon Linoff

1,242,037
58
646
786

Have you ever read Jeff Moden's article about using a rcte for counting like this? he demonstrates that under the hood it is RBAR processing. http://www.sqlservercentral.com/articles/T-SQL/74118/ – Sean Lange Jan 31 '18 at 15:00
seems it should fit into my requirement, Thanks. – LearnSQL Jan 31 '18 at 15:05
@sean lange: very good article. – Christian4145 Jan 31 '18 at 15:09
@learnSQL: There is a lot to learn in this article ;-) – Christian4145 Jan 31 '18 at 15:10
@SeanLange . . . Interesting. I note that Aaron Bertrand doesn't have as harsh a view of recursive CTEs for counting: https://sqlblog.org/2009/10/07/bad-habits-to-kick-using-loops-to-populate-large-tables. In any case, I freely admit that a "tally" or "numbers" table is likely to be more efficient. Of course, SQL Server could implement a function such as `generate_series()` rendering all these discussions obsolete. – Gordon Linoff Jan 31 '18 at 16:22

score 1 · Answer 2 · answered Jan 31 '18 at 15:03

A recursive CTE less solution would look something like....

Declare @t table (NumberFrom INT ,  NumberTo INT)
INSERT INTO @t values
(1 , 2),
(3 , 3),
(5 , 7),
(9 , 11)

Select * 
from @t t
CROSS APPLY (  SELECT TOP ( IIF(
                                ABS(t.NumberTo - t.NumberFrom) = 0 
                                , 1 
                                , ABS(t.NumberTo - t.NumberFrom) + 1) )
                        t.NumberFrom +  ROW_NUMBER() OVER (ORDER BY (Select NULL)) - 1 Vals
                FROM master..spt_values x CROSS JOIN master..spt_values y
            ) c

For SQL Server older than 2012

I used IIF() function before, if you are using SQL Server 2005 or 2008 you can use Case statement inside the TOP clause, something like this...

Declare @t table (NumberFrom INT ,  NumberTo INT)
INSERT INTO @t values
(1 , 2),
(3 , 3),
(5 , 7),
(9 , 11)

Select * 
from @t t
CROSS APPLY (  SELECT TOP ( CASE WHEN
                                ABS(t.NumberTo - t.NumberFrom) = 0 
                                THEN 1 
                                ELSE ABS(t.NumberTo - t.NumberFrom) + 1 END )
                        t.NumberFrom +  ROW_NUMBER() OVER (ORDER BY (Select NULL)) - 1 Vals
                FROM master..spt_values x CROSS JOIN master..spt_values y
            ) c

score 1 · Answer 3 · answered Jan 31 '18 at 15:07

Another option is to use an ad-hoc tally table in concert with a CROSS APPLY.

Example

Select B.* 
 From  YourTable A
 Cross Apply (
                Select Top (NumberTo-NumberFrom+1) N=NumberFrom-1+Row_Number() Over (Order By (Select NULL)) 
                 From  master..spt_values n1 ,master..spt_values n2 
             ) B

Note: remove ,master..spt_values n2 if the span will not exceed 2,500.

Returns

score -1 · Answer 4 · answered Jan 31 '18 at 14:54

Try This approach

DECLARE @T TABLE
(
    NumRange VARCHAR(20)
)

INSERT INTO @T 
VALUES('1-2'),('5-7'),('3-3')

;WITH CTE
AS
(
    SELECT
        StrtVal = CAST(SUBSTRING(NumRange,1,CHARINDEX('-',NumRange)-1) AS INT),
        ENdVal = CAST(SUBSTRING(NumRange,CHARINDEX('-',NumRange)+1,LEN(NumRange)) AS INT)
        FROM @T

    UNION ALL

    SELECT
        StrtVal = StrtVal+1,
        ENdVal
        FROM CTE
            WHERE StrtVal<ENdVal
)
SELECT
    StrtVal
    FROM CTE
        ORDER BY StrtVal

Thank you all for many valuable suggestions. this will help in next time. — LearnSQL, Jan 31 '18 at 15:06

Split the number range to individual number rows

4 Answers4