How to create numbers range of numbers between two numbers?

Question

So i have Table and i have Columns NumberMin(minimal number ) and i have NumberMax(maximal number) i have many records i want to select all data and in new column i had numbers beetwen this number min and number max like this this is table

ID Name NumberMin NumberMax 
13 Jhon 10        14

and when i select i want to be like this

Name NumberMin NumberMax newcolum
Jhon 10        14        10
Jhon 10        14        11
Jhon 10        14        12
Jhon 10        14        13

i tried this code but didn't worked like this

;with numcte  
AS  
(  
  SELECT NumbMin as[SEQUENCE]  
  UNION all  
  SELECT Name,NumbMin,NumbMax,[SEQUENCE] + 1 FROM Table WHERE NumbMin  <NumbMax 
)      
SELECT * FROM numcte

Does this answer your question? [What is the best way to create and populate a numbers table?](https://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table) — SMor, May 04 '20 at 12:59

John Cappelletti · Answer 1 · 2020-05-04T13:07:16.323

2

Yet another option is an ad-hoc tally table.

Not sure if you wanted to exclude the top value (14). If so, just remove the +1 in the Select top(...)

Example

Select A.* 
      ,NewColumn = NumberMin+B.N-1
 From  YourTable A
 Cross Apply (
                Select Top (NumberMax-NumberMin+1) N=row_number() Over (Order By (Select NULL)) From master..spt_values n1, master..spt_values n2
             ) B

Returns

edited May 04 '20 at 13:07

answered May 04 '20 at 13:02

John Cappelletti

79,615
7
44
66

@TheImpaler You may be rright. I did, however, add a "Not sure" – John Cappelletti May 04 '20 at 13:06
@TheImpaler Seemed odd that OP has min but not max. – John Cappelletti May 04 '20 at 13:06
Interesting. I didn't know the table `master..spt_values` existed. Can come in handy. – The Impaler May 04 '20 at 13:10
@TheImpaler Any table of appropriate size/rows will do.... notice the Order By (Select null) – John Cappelletti May 04 '20 at 13:12

GMB · Accepted Answer · 2020-05-04T13:05:29.220

The problem with your query is that both the anchor and the recursive part of the query must have the same number of columns (and identical datatypes). Your anchor has just one column (numberMin), while the recursive part has 4 columns, so the query errors.

Consider:

with numcte as (
    select id, name, numberMin, numberMax, numberMin newcol from mytable
    union all
    select id, name, numberMin, numberMax, newcol + 1
    from numcte
    where newcol < numberMax - 1
)
select * from numcte

The where condition prevents the last value of newcol to actually reach numberMax - that's a bit unusual, but that's what your desired result is showing.

Note that SQL Server by default restricts the number of iteration of a recursive query to 100. If you need to handle more than that, you need to add option (maxrecursion 0) at the end of the query.

score 1 · Answer 3 · answered May 04 '20 at 13:00

You can use a recursive CTE to do this.

For example:

with
x as (
  select ID, Name, NumberMin, NumberMax, NumberMin as cur from t
  UNION ALL
  select ID, Name, NumberMin, NumberMax, cur + 1
  from x
  where cur < NumberMax - 1
)
select * from x

Result:

ID  Name  NumberMin  NumberMax  cur
--  ----  ---------  ---------  --- 
13  John         10         14   10
13  John         10         14   11
13  John         10         14   12
13  John         10         14   13

See running example at DB<>Fiddle.

How to create numbers range of numbers between two numbers?

3 Answers3