SQL中合併多行記錄的方法總彙

——前幾天還在抱怨：sql只有sum(數值)，不能sum(字串)

——如果不是分組統計，用select @values = @values + "," + value也是可以的

——但是如果是分組sum(字串)，肯定不行了！

——下面是用函式實現的分組sum(字串)，只是比較“死”：表名、分組依據、待累加的字串欄位都需要預先寫死！

http://blog.csdn.net/dobear_0922/archive/2008/04/22/2313839.aspx

SQL中合併多行記錄的方法總彙

-- =============================================================================

-- Title: 在SQL中分類合併資料行

-- Author: dobear Mail(MSN): [email protected]

-- Environment: Vista + SQL2005

-- Date: 2008-04-22

-- =============================================================================

--1. 建立表，新增測試資料

CREATE TABLE tb(id int, [value] varchar(10))

INSERT tb SELECT 1, "aa"

UNION ALL SELECT 1, "bb"

UNION ALL SELECT 2, "aaa"

UNION ALL SELECT 2, "bbb"

UNION ALL SELECT 2, "ccc"

--SELECT * FROM tb

/**//*

id value

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

1 aa

1 bb

2 aaa

2 bbb

2 ccc

(5 row(s) affected)

*/

--2 在SQL2000只能用自定義函式實現

----2.1 建立合併函式fn_strSum，根據id合併value值

GO

CREATE FUNCTION dbo.fn_strSum(@id int)

RETURNS varchar(8000)

AS

BEGIN

DECLARE @values varchar(8000)

SET @values = ""

SELECT @values = @values + "," + value FROM tb WHERE id=@id

RETURN STUFF(@values, 1, 1, "")

END

GO

-- 呼叫函式

SELECT id, VALUE = dbo.fn_strSum(id) FROM tb GROUP BY id

DROP FUNCTION dbo.fn_strSum

----2.2 建立合併函式fn_strSum2，根據id合併value值

GO

CREATE FUNCTION dbo.fn_strSum2(@id int)

RETURNS varchar(8000)

AS

BEGIN

DECLARE @values varchar(8000)

SELECT @values = isnull(@values + ",", "") + value FROM tb WHERE id=@id

RETURN @values

END

GO

-- 呼叫函式

SELECT id, VALUE = dbo.fn_strSum2(id) FROM tb GROUP BY id

DROP FUNCTION dbo.fn_strSum2

--3 在SQL2005中的新解法

----3.1 使用OUTER APPLY

SELECT *

FROM (SELECT DISTINCT id FROM tb) A OUTER APPLY(

SELECT [values]= STUFF(REPLACE(REPLACE(

(

SELECT value FROM tb N

WHERE id = A.id

FOR XML AUTO

), "<N value="", ","), ""/>", ""), 1, 1, "")

)N

----3.2 使用XML

SELECT id, [values]=STUFF((SELECT ","+[value] FROM tb t WHERE id=tb.id FOR XML PATH("")), 1, 1, "")

FROM tb

GROUP BY id

drop table tb

/**//*

id values

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

1 aa,bb

2 aaa,bbb,ccc

(2 row(s) affected)

*/