INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...I've gotten solutions within a day - it saved a lot of time and actually got me one ATTABOY from my boss..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Programmers > DBMS Packages > Microsoft SQL Server: Programming Forum

List numbers between two integers with comma separated list

CODE

create table #temp (low int, high int, list varchar(100))
go
insert into #temp (low, high) values (1, 10)
insert into #temp (low, high) values (15, 25)
insert into #temp (low, high) values (22, 28)

Is there a way I can update the 'list' column with a comma separated list of the numbers between the low and high numbers?

I'm hoping to end up with

Low High List
1 10 1,2,3,4,5,6,7,8,9,10
15 25 15,16,17,18,19,20,21,22,23,24,25
22 28 22,23,24,25,26,27,28

Thanks!

Brian

imex (Programmer)

2 Jul 12 20:50

Hi,

Try:

CODE -->

with CTE as
(
    select low, high, low as number
    from #temp
    
    union all
    
    select low, high, (number + 1) as number
    from CTE
    where number < high
)

update #temp set list = STUFF( (SELECT ',' + CAST(c.number as varchar)
                                FROM CTE as c
                                WHERE (c.low = t.low) and (c.high = t.high)
                                FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
                               ,1, 1, '')
from #temp as t

Hope this helps.

http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento

bmacbmac (IS/IT--Management)

3 Jul 12 2:23

Thanks imex,

That worked great. But I have some pretty large ranges (38-390). On these large ranges I get the error message:

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Is there way to increase the recursion?

imex (Programmer)

3 Jul 12 10:04

Try adding a line similar to that following in the end:

CODE -->

OPTION (MAXRECURSION 400);

Hope this helps.

http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento

simian336 (Programmer)

3 Jul 12 10:33

First Create a numbers table (there are lots of ways of doing this. This is one I found with a quick search.)

CREATE TABLE [Numbers]
([Number] [int])

Declare @cnt int

Select @cnt=0

SET NOCOUNT ON
while (@cnt<10000)

BEGIN
INSERT INTO NUMBERS(NUMBER)
SELECT @cnt
SELECT @cnt=@cnt+1
End

--**********************************

Then

select low, high,
(select CAST(number as varchar) +', '
FROM NUMBERS
WHERE number > t.low and number < t.high
FOR XML PATH(''))
from #temp t

Simi

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft SQL Server: Programming

Member Login

Come Join Us!

LINK TO THIS FORUM!

Tek-Tips LinkedIn® Group

Partner With Us!

Feedback

Geography

Tek-Tips Shirts!

List numbers between two integers with comma separated list

CODE

CODE -->

CODE -->

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Back To Forum

Join Tek-Tips® Today!

Tek-Tips LinkedIn^® Group

Join Tek-Tips^® Today!