在項(xiàng)目開(kāi)發(fā)中，有時(shí)會(huì)碰到將列記錄合并為一行的情況，例如根據(jù)地區(qū)將人員姓名合并，或根據(jù)拼音首字母合并城市等，下面就以根據(jù)地區(qū)將人員姓名合并為例，詳細(xì)講一下合并的方法。

首先，先建一個(gè)表，并添加一些數(shù)據(jù)，建表代碼如下：

If OBJECT_ID(N'Demo') Is Not Null Begin Drop Table Demo EndElse Begin Create Table Demo( Area nvarchar(30), Name nvarchar(20))

Insert Into Demo(Area,Name) Values(N'北京',N'張三'), (N'上海',N'李四'), (N'深圳',N'王五'), (N'深圳',N'錢(qián)六'), (N'北京',N'趙七'), (N'北京','Tom'), (N'上海','Amy'), (N'北京','Joe'), (N'深圳','Leo') EndGo

建完后查詢(xún)一下，可見(jiàn)表中數(shù)據(jù)如下：

如果僅將Name列合并，不遵循任何條件的話(huà)，我們可以采用兩種方法，第一種就是采用FOR xml PATH方式，代碼如下：

SELECT ','+Name FROM dbo.Demo FOR XML PATH('')

運(yùn)行結(jié)果如下：

關(guān)于FOR XML PATH的詳細(xì)介紹可參考MSDN：搭配 FOR XML 使用 PATH 模式

第二種方法就是定義一個(gè)變量用來(lái)裝載查詢(xún)的結(jié)果，代碼如下：

Declare @NameCollection nvarchar(500)Select @NameCollection=ISNULL(@NameCollection+',','')+Name From dbo.DemoSelect @NameCollection as NameCollection

運(yùn)行結(jié)果如下：

加了ISNULL是因?yàn)樽铋_(kāi)始變量@NameCollection為NULL，為了避免“張三”前多一個(gè)逗號(hào)（“,”）而采用的替換。

上面講了在無(wú)條件的情況下合并一列，但是在項(xiàng)目中幾乎不會(huì)遇到這樣的情況，一般都是根據(jù)某一列來(lái)合并另一列的數(shù)據(jù)，例如我們現(xiàn)在要根據(jù)Area將Name合并，得到這樣的結(jié)果：

有了上面的基礎(chǔ)，要合并成這樣的數(shù)據(jù)就容易了，我們只需要針對(duì)Area列采用聚合GROUP BY或取不重復(fù)值DISTINCT，然后根據(jù)Area列合并Name列，有了思路，下面就來(lái)說(shuō)說(shuō)如何實(shí)現(xiàn)，首先還是采用FOR XML PATH方式，結(jié)合自連接，首先先按Area列對(duì)Name列進(jìn)行合并，代碼如下：

SELECT Area,(SELECT ','+Name FROM dbo.Demo WHERE Area = t.Area FOR XML PATH(''))AS NameCollection FROM dbo.Demo AS t

運(yùn)行結(jié)果如下：

現(xiàn)在有兩點(diǎn)還沒(méi)實(shí)現(xiàn)，第一是結(jié)果重復(fù)了，第二是NameCollection列最開(kāi)始都多了一個(gè)逗號(hào)，先去掉逗號(hào)，采用STUFF 函數(shù)來(lái)進(jìn)行替換，代碼修改如下：

SELECT Area,STUFF((SELECT ','+Name FROM dbo.Demo WHERE Area = t.Area FOR XML PATH('')),1,1,'')AS NameCollection FROM dbo.Demo AS t

現(xiàn)在運(yùn)行后結(jié)果如下：

下面就剩下去掉重復(fù)數(shù)據(jù)了，分別采用GROUP BY和DISTINCT，代碼如下：

SELECT DISTINCT Area,STUFF((SELECT ','+Name FROM dbo.Demo WHERE Area = t.Area FOR XML PATH('')),1,1,'')AS NameCollection FROM dbo.Demo AS t

SELECT Area,STUFF((SELECT ','+Name FROM dbo.Demo WHERE Area = t.Area FOR XML PATH('')),1,1,'')AS NameCollection FROM dbo.Demo AS t GROUP BY Area

關(guān)于STUFF函數(shù)可以參考MSDN介紹：STUFF函數(shù)

運(yùn)行結(jié)果即為最終我們需要的結(jié)果，最開(kāi)始在上面講到了一種用變量來(lái)裝載查詢(xún)結(jié)果實(shí)現(xiàn)合并一列的方法，下面詳細(xì)介紹如何采用上述方法來(lái)實(shí)現(xiàn)我們的需求，我們可以根據(jù)上面的方法建一個(gè)函數(shù)，傳入一個(gè)Area參數(shù)，根據(jù)Area來(lái)進(jìn)行合并，返回合并值，函數(shù)如下：

CREATE FUNCTION MergeByColumn( -- Add the parameters for the function here @Area nvarchar(30))RETURNS nvarchar(500)ASBEGIN -- Declare the return variable here DECLARE @NC nvarchar(500)

-- Add the T-SQL statements to compute the return value here SELECT @NC=ISNULL(@NC+',','')+Name FROM dbo.Demo WHERE Area=@Area

-- Return the result of the function RETURN @NC

ENDGO

建好后測(cè)試下，以傳入?yún)?shù)為“北京”為例，運(yùn)行如下代碼：

SELECT dbo.MergeByColumn('北京') AS NameCollection

得到結(jié)果如下：

現(xiàn)在只需將Area列也加入查詢(xún)即可，修改代碼如下：

SELECT Area,dbo.MergeByColumn(Area) AS NameCollection From dbo.Demo

現(xiàn)在也得到了重復(fù)的結(jié)果，如下：

去重復(fù)同樣可以用GROUP BY和DISTINCT，代碼如下，即可以得到我們最終的結(jié)果：

SELECT DISTINCT Area,dbo.MergeByColumn(Area) AS NameCollection From dbo.Demo

SELECT Area,dbo.MergeByColumn(Area) AS NameCollection From dbo.Demo GROUP BY Area

本文轉(zhuǎn)自：http://www.cnblogs.com/leolis/p/3977569.html