多条记录合并为一条,将连续的编号写成“起始编号-结束编号”的形式

多条记录合并为一条，同时将连续的编号写成“起始编号-结束编号”的形式

表 a：
编号    值
000055 2
000057 2
000059 2
000060 2
000061 2
000062 2
000063 2
000064 2
000065 3
000066 1
000600 1

要求得到如下结果：
编号                          值
000055,000057,000059-000064  2
000065                        3
000066,000600                1
将值相同的记录合并成一行，同时将连续的编号写成“起始编号-结束编号”的形式。

SQL code----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-09-17 16:54:11
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
--    Nov 24 2008 13:01:59
--    Copyright (c) 1988-2005 Microsoft Corporation
--    Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([编号] varchar(6),[值] 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
--------------开始查询--------------------------
select 值, [编号]=stuff((select ','+[编号] from tb t where 值=tb.值 for xml path('')), 1, 1, '')
from tb
group by 值
----------------结果----------------------------
/* 值           编号
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           000066,000600
2           000055,000057,000059,000060,000061,000062,000063,000064
3           000065

(3 行受影响)
*/

SQL code-------------------------------------
--  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 : 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.值 ORDER BY ABS(rowid) FOR XML PATH('')),1,1,'') AS 编号,
    值
FROM Liang2 AS A
GROUP BY 值

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