List numbers between two integers with comma separated list

[bmacbmac]

Hello. Assuming the following table/data:

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]

Hi,

Try:

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.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]

 

[bmacbmac]

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]

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

OPTION (MAXRECURSION 400);

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]

 

[simian336]

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