--生成已用編號分布字符串的函數
CREATE FUNCTION f_GetStrSeries(@col1 varchar(10))
RETURNS varchar(8000)
AS
BEGIN
	DECLARE @re varchar(8000),@pid int
	SELECT @re='',@pid=-1
	SELECT @re=CASE 
			WHEN col2=@pid+1 THEN @re
			ELSE @re
				+CASE 
					WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)=@pid THEN ''
					ELSE CAST(-@pid as varchar)
				END
				+','+CAST(col2 as varchar) 
			END,
		@pid=col2
	FROM tb
	WHERE col1=@col1
	ORDER BY col2
	RETURN(STUFF(@re,1,2,'')
		+CASE 
			WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)=@pid THEN ''
			ELSE CAST(-@pid as varchar)
		END)
END
GO

--生成缺號分布字符串的函數
CREATE FUNCTION f_GetStrNSeries(@col1 varchar(10))
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @re varchar(8000),@pid int
	SELECT @re='',@pid=0
	SELECT @re=CASE 
			WHEN col2=@pid+1 THEN @re
			ELSE @re+','+CAST(@pid+1 as varchar)
				+CASE 
					WHEN @pid+1=col2-1 THEN ''
					ELSE CAST(1-col2 as varchar)
				END
			END,
		@pid=col2
	FROM tb
	WHERE col1=@col1
	ORDER BY col2
	RETURN(STUFF(@re,1,1,''))
END
GO

--調用測試
--測試數據
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',2
UNION ALL SELECT 'a',3
UNION ALL SELECT 'a',5
UNION ALL SELECT 'a',8
UNION ALL SELECT 'a',9
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',5
UNION ALL SELECT 'b',6
UNION ALL SELECT 'b',7

--查詢
SELECT col1,
	col2_Series=dbo.f_GetStrSeries(col1),
	col2_Series=dbo.f_GetStrNSeries(col1)
FROM tb
GROUP BY col1
/*--結果
col1       col2_Series       col2_Series 
-------------- ------------------------ --------------
a          2-3,5,8-9        1,4,6-7
b          1,5-7           2-4
--*/