Data Ranges - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Data Ranges

Author

Topic

warren_goodwin
Starting Member

2 Posts

Posted - 2002-07-22 : 11:52:48

If I have a table with 4 columns and the following data:

StartNumber EndNumber Name Description
1 3 a aaa
7 7 b bbb
20 24 c ccc

and the output required is:

Number Name Description
1 a aaa
2 a aaa
3 a aaa
7 b bbb
20 c ccc
21 c ccc
22 c ccc
23 c ccc
24 c ccc

I want to be able to produce this output from a strored procedure (or single SQL command) in SQL Server. What is the SQL statement that I require?

Many thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-22 : 11:55:56

Create a sequence table, which is a plain old table with a single column of integer values. More on them here:

http://www.sqlteam.com/item.asp?ItemID=5857
http://www.sqlteam.com/item.asp?ItemID=2652

Then use the following:

SELECT S.ID AS Number, A.Name, A.Description
FROM myTable A, Sequence S
WHERE S.ID Between A.StartNumber AND A.EndNumber

warren_goodwin
Starting Member

2 Posts

Posted - 2002-07-22 : 11:58:10

Is there any other way, as the number is actually a telephone number column (string really, not numeric) and therefore the list would be very large.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-22 : 12:08:21

Can you post your EXACT table structure, and show some sample data?

The sequence table is still going to be the easiest and fastest way to do this, but I need more detail on your tables before I can get a solution.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-07-22 : 15:34:30

quote:

Is there any other way, as the number is actually a telephone number column (string really, not numeric) and therefore the list would be very large.

The size of the numbers isn't an issue -- just work relative to StartNumber:

SELECT S.ID+A.StartNumber AS Number, A.Name, A.Description
FROM myTable A, Sequence S
WHERE S.ID Between 0 AND A.EndNumber - A.StartNumber

If A.EndNumber - A.StartNumber is larger than the largest Sequence.ID then you've got problems any way you cut it! Even so, a Sequence table with 100000 rows is perfectly reasonable, and you can always cross join it with itself if you need more.

Edited by - Arnold Fribble on 07/22/2002 15:40:34

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources