hi to all,

i have table like below

from	end
200	204
300	302
10	10
170	172

here i want to pass from and end as loop wise

my expected output is 

value
200
201
202
203
204
300
301
302
10
170
171
172

create table test (id int identity(1,1), [from] int, [to] int)
Insert into test values(200,204),(300,302),(10,10),(170,172)

;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n)-1 FROM Num2)

Select n from test

Cross apply (select n from nums) d(n)

Where n>=[From] and n<=[to]
Order by id


drop table test

• Proposed as answer by Naomi NModerator 16 hours 13 minutes ago
• Marked as answer by gvrspk veni 2 hours 44 minutes ago

Hi ,

  In Master database we a table which can be used. But not sure upto what numbers it is available in this table as I am unable to connect to SQL Server database and check. Please consider the below as a pseudo code

select s.number
  from master.dbo.spt_values s
  join yourtable
    on s.number >=  yourtable.start and s.number <=  yourtable.end  
WHERE Type = 'P'

Give this a go:

DECLARE @table TABLE (fromInt INT, toInt INT)
INSERT INTO @table (fromInt, toInt) VALUES
(200, 204),
(300, 302),
(10	, 10 ),
(170, 172)

;WITH rCTE AS (
SELECT fromInt AS value, toInt
  FROM @table 
UNION ALL
SELECT value + 1, toInt
  FROM rCTE
 WHERE value + 1 <= toInt
)

SELECT value
  FROM rCTE
 ORDER BY value

It is an always good idea to have 2 permanent tables in the database - Numbers and Calendar. Once you have these 2 tables, all such problems become a breeze.

>> I have table like below <<

NO! That is a list and not a table! Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data (https://xkcd.com/1179/). We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL. 

If you had read the forum rules, you might have posted this DDL? See the keys? See the data types? A range constraint? 

CREATE TABLE Something_List
(start_value INTEGER NOT NULL PRIMARY KEY
 end_value INTEGER NOT NULL,
 CHECK(start_value <= end_value)
);

INSERT INTO Something_List
VALUES
(200, 204),
(300, 302),
(10, 10),
(170, 172);

>> here I want to pass from and end as loop wise <<

SQL is a declarative language; it does not use loops. That is part of procedural programing. One of the most common SQL idioms is the Series table (also called Numbers, Sequence (until it became a construct in SQL)). It is a table of a series of integers from 1 to (n), with a clustered index on that column and no fill factors. Optionally, it may have other columns with tricky functions that use an integer parameter. 

SELECT S.seq 
  FROM Series AS S, 
       Something_List AS L 
 WHERE S.seq BETWEEN L.start_value AND L.end_v

hi celko,

thanks for your comments . its helpful. i am using SQL Server 2008 R2 i cant access Series table.