-------------------------------------
--  Author : liangCK 梁爱兰
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-09-17 16:53:30
-------------------------------------
 
--> 生成测试数据: @tb
DECLARE @tb TABLE (编号 varchar(6),值 int)
INSERT INTO @tb
SELECT '000055',2 UNION ALL
SELECT '000057',2 UNION ALL
SELECT '000059',2 UNION ALL
SELECT '000060',2 UNION ALL
SELECT '000061',2 UNION ALL
SELECT '000062',2 UNION ALL
SELECT '000063',2 UNION ALL
SELECT '000064',2 UNION ALL
SELECT '000065',3 UNION ALL
SELECT '000066',1 UNION ALL
SELECT '000600',1

--SQL查询如下:

;WITH Liang AS
(
    SELECT ROW_NUMBER() OVER(PARTITION BY 值 ORDER BY 值) - 编号 AS rowid,*
    FROM @tb
),
Liang2 AS
(
    SELECT 值,rowid,CASE WHEN COUNT(*) > 1 THEN RTRIM(MIN(编号))+'~'+RTRIM(MAX(编号))
                           ELSE RTRIM(MIN(编号)) END AS flag 
    FROM Liang 
    GROUP BY 值,rowid
)
SELECT 
    STUFF((SELECT ',' + flag AS [text()] FROM Liang2 
     WHERE 值 = A.值 FOR XML PATH('')),1,1,'') AS 编号,
    值
FROM Liang2 AS A
GROUP BY 值

/*
编号    值
000600,000066    1
000059~000064,000057,000055    2
000065    3
*/

------解决方案--------------------
SQL code

/*---------------------------------
--  Author : htl258(Tony)
--  Date   : 2009-09-17 16:53:11
--  Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) 
    Mar 29 2009 10:27:29 
    Copyright (c) 1988-2008 Microsoft Corporation
    Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

---------------------------------*/
--> 生成测试数据表:tb

IF NOT OBJECT_ID('[tb]') IS NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb]([编号] NVARCHAR(10),[值] INT)
INSERT [tb]
SELECT '000055',2 UNION ALL
SELECT '000057',2 UNION ALL
SELECT '000059',2 UNION ALL
SELECT '000060',2 UNION ALL
SELECT '000061',2 UNION ALL
SELECT '000062',2 UNION ALL
SELECT '000063',2 UNION ALL
SELECT '000064',2 UNION ALL
SELECT '000065',3 UNION ALL
SELECT '000066',1 UNION ALL
SELECT '000600',1
GO
--SELECT * FROM [tb]

-->SQL查询如下:
;WITH t AS
(
    SELECT CASE WHEN a.编号=b.编号 THEN a.编号 ELSE a.编号+'-'+b.编号 END AS 编号,a.值
    FROM (
        SELECT rn=ROW_NUMBER()OVER(ORDER BY GETDATE()),* 
        FROM tb t 
        WHERE NOT EXISTS(
            SELECT 1 FROM tb 
            WHERE [值]=t.值 AND [编号]=t.[编号]+1)
        ) AS a
        JOIN (
        SELECT rn=ROW_NUMBER()OVER(ORDER BY GETDATE()),* 
        FROM tb t 
        WHERE NOT EXISTS(
            SELECT 1 FROM tb 
            WHERE [值]=t.值 AND t.[编号]=[编号]+1)
        ) AS b
            ON a.rn=b.rn
) 
SELECT DISTINCT 编号=STUFF((SELECT ','+编号 FROM t WHERE 值=a.值 FOR XML PATH('')),1,1,''),值
FROM t a
/*
编号    值
000055,000057,000064-000059    2
000065    3
000066,000600    1
*/

------解决方案--------------------
SQL code
sql2000:
CREATE TABLE tgss(a VARCHAR(20),b INT)
INSERT tgss SELECT '000055',2 
union all select '000057', 2 
union all select '000059', 2 
union all select '000060', 2 
union all select '000061', 2 
union all select '000062', 2 
union all select '000063', 2 
union all select '000064', 2 
union all select '000065', 3 
union all select '000066', 1 
union all select '000600', 1

ALTER TABLE tgss ADD g INT


DECLARE @i INT,@l VARCHAR(20),@sql VARCHAR(2000) 
SET @i=0

UPDATE tgss SET @i=CASE WHEN a*1=@l*1+1 THEN @i ELSE @i+1 end,@l=a,g=@i

go
SELECT b,g,CASE WHEN MIN(a)=MAX(a) THEN MIN(a) ELSE  MIN(a)+'-'+MAX(a)  END  e into tgssA FROM tgss 
GROUP