合并列值整理

数据：

id    col

----- ------

1     a

1     b

2     a

2     b

2     c

 

期望得到结果：

id     col

------ -----------

1      a,b

2      a,b,c

 

----------------------------------------------------------------------------------

-- Author : htl258(Tony)

-- Date   : 2009-03-15 10:19:22

-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)

--          Jul  9 2008 14:43:34

--          Copyright (c) 1988-2008 Microsoft Corporation

--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

-- Blog   : http://blog.csdn.net/htl258

----------------------------------------------------------------------------------

--> 生成测试数据表: [tb]

IF OBJECT_ID('[tb]') IS NOT NULL

    DROP TABLE [tb]

GO

CREATE TABLE [tb] ([id] [int],[col] [nvarchar](10))

INSERT INTO [tb]

SELECT '1','a' UNION ALL

SELECT '1','b' UNION ALL

SELECT '2','a' UNION ALL

SELECT '2','b' UNION ALL

SELECT '2','c'

 

--SELECT * FROM [tb]

 

-->SQL查询如下:

--SQL2000以上版本:

IF OBJECT_ID('dbo.f_str')>0

    DROP FUNCTION dbo.f_str

GO

CREATE FUNCTION dbo.f_str

(

    @id INT

)

RETURNS VARCHAR(1000)

AS

BEGIN

    DECLARE @r VARCHAR(1000)

    SELECT @r = ISNULL(@r+',', '') + col

    FROM tb

    WHERE id = @id

    RETURN @r

END

GO

SELECT DISTINCT id,dbo.f_str(id) col FROM tb

/*

id          col

----------- ---------------------

1           a,b

2           a,b,c

 

(2 行受影响)

*/

 

-->SQL2005以上版本：

SELECT DISTINCT id,

    STUFF(

             (

                 SELECT ','+col

                 FROM tb t

                 WHERE id = tb.id FOR XML PATH('')

             ), 1, 1, ''

         ) AS col

FROM tb

/*

id          col

----------- ---------------------

1           a,b

2           a,b,c

 

(2 行受影响)

*/