CREATE TABLE #t1 ([col] int)
GO
insert into #t1 SELECT 1;
insert into #t1 SELECT 3;
insert into #t1 SELECT 4;
insert into #t1 SELECT 5;
insert into #t1 SELECT 8;
insert into #t1 SELECT 9;
insert into #t1 SELECT 10;
insert into #t1 SELECT 15;
insert into #t1 SELECT 17;
insert into #t1 SELECT 18;
GO
DECLARE @iStr varchar(400);
WITH cte (rowNo, col) AS (
SELECT ROW_NUMBER() over (order by col asc) AS rowNo, col
FROM #t1
)
SELECT @iStr= ISNULL(@iStr, '')+case
WHEN x.rowNo IS NULL THEN CONVERT(varchar(4), y.col) -- First number
WHEN x.col+1= y.col AND y.col+1= ISNULL(z.col, -1) THEN ''
WHEN x.col+1= y.col AND y.col+1!= ISNULL(z.col, -1) THEN '-'+CONVERT(varchar(4), y.col)
WHEN x.col+1!= y.col THEN ', '+CONVERT(varchar(4), y.col)
ELSE '' end
FROM cte as x
RIGHT JOIN cte as y ON x.rowNo= y.rowNo- 1
LEFT JOIN cte as z ON y.rowNo+ 1= z.rowNo;
SELECT @iStr;
Results:1, 3-5, 8-10, 15, 17-18; |
發表於 2010-1-13 13:23
|