--说明:
--比如1-3是连续的，作为1组，5-6是连续的作为1组，9单独也作为1组，分组后合并VALUE1的值，用逗号隔开。

--原始数据（请考虑支持10000行时的高效写法）
DECLARE @TB TABLE(ID INT,VALUE1 VARCHAR(10))
INSERT INTO @TB
SELECT 2,'b' UNION ALL
SELECT 1,'a' UNION ALL
SELECT 3,'c' UNION ALL
SELECT 5,'e' UNION ALL
SELECT 6,'x' UNION ALL
SELECT 9,'y'

--目标结果（如果难度过大可考虑不合并VALUE1，给出_MIN和_MAX即可）
SELECT 1 AS _MIN,3 AS _MAX,'a,b,c' AS ALL_VALUE UNION ALL
SELECT 5,5,'e,x' UNION ALL
SELECT 9,9,'y'

create table tb(
value int,
name varchar(5)
)
INSERT INTO tb
SELECT 2,'b' UNION ALL
SELECT 1,'a' UNION ALL
SELECT 3,'c' UNION ALL
SELECT 5,'e' UNION ALL
SELECT 6,'x' UNION ALL
SELECT 9,'y'
with t
as(
select 
px=value-ROW_NUMBER()over(order by value),
* from tb
)
SELECT MIN(a.value)min_value,
MAX(a.value) max_value,
name=STUFF((SELECT ','+t.name
FROM t 
WHERE a.px=px FOR XML PATH('')),1,1,'')
FROM t a
GROUP BY a.px
/*
min_value    max_value    name
1    3    a,b,c
5    6    e,x
9    9    y
*/