create function dbo.get_ranges()
returns varchar(max)
as
begin
return
stuff(
(select ',' + cast(i1.item as varchar) + isnull('-' + cast(
(select min(i2.item)
from items i2
where not exists(select * from items where item=i2.item+1) and i2.item>i1.item) as varchar),'')
from items i1
where not exists(select * from items where item=i1.item-1)
for xml path(''),type).value('.','varchar(max)'),
1, 1, '')
end
go
create table items (item int)
insert into items (item) select 0 union select 1 union select 2 union select 3 union select 4 union select 7 union select 8 union select 10
select dbo.get_ranges()