CODEWITHSUNDEEP
sql-serversql-server-2008
Shilpa Praneesh
Shilpa Praneesh

Reputation: 255

How to get all numbers between a range

I have a table as below

Id     RFrom      RTo
....  .......    .....
1       10         14
1       22         25
2       100        102
2       176        180

I want to get all numbers between each RFrom and RTo for each Id. My expected result is as follows

Id     NUMS
....  ......
1       10
1       11
1       12
1       13
1       14
1       22
1       23
1       24
1       25
2       100
2       101
2       102
2       176
2       177
2       178
2       179
2       180

Do I have to use cursor to achieve this?

Upvotes: 1

Views: 932

Answers (3)

Pரதீப்
Pரதீப்

Reputation: 93754

Create a tally table using stacked CTE which will have better performance when compared to recursive CTE

declare @min int
select @min= min(RFrom) from yourtable
;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
), -- 10
e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b), -- 10*10
e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2) -- 10*100
SELECT b.id,
       a.n
FROM   yourtable b
       JOIN (SELECT n = Row_number()OVER (ORDER BY n)+ @min-1
             FROM   e3)a
         ON a.n BETWEEN b.RFrom AND b.RTo
ORDER  BY n;

Check here for info

Upvotes: 0

sgeddes
sgeddes

Reputation: 62861

Another option would be to use a numbers table with a join -- recursion can be time consuming.

There are several options to create a numbers table (I'd recommend creating a permanent one), but here's a temp one created with a common-table-expression:

with numberstable as (
  select top 10000 row_number() over(order by t1.number) as number
  from master..spt_values t1 
      cross join master..spt_values t2
  )
select yt.id,
  nt.number
from yourtable yt
  join numberstable nt on nt.number between yt.rfrom and yt.rto

Upvotes: 1

Sarath Subramanian
Sarath Subramanian

Reputation: 21401

Here is your sample table

SELECT * INTO #TEMP FROM
(
    SELECT 1 ID, 10 RFROM, 14 RTO
    UNION ALL
    SELECT 1,       22,         25
    UNION ALL
    SELECT 2,       100,        102
    UNION ALL
    SELECT 2,       176,        180
)TAB

You need to use recursion for each Id to get the result

;WITH CTE AS
(
   SELECT ID,RFROM RFROM1,RTO RTO1 
   FROM #TEMP  
   UNION ALL
   SELECT T.ID,RFROM1+1,RTO1 
   FROM #TEMP T
   JOIN CTE ON CTE.ID = T.ID
   WHERE RFROM1 < RTO1
)
SELECT DISTINCT ID,RFROM1 NUMS 
FROM CTE

Upvotes: 2

Related Questions