sql里將重復行數(shù)據(jù)合并為一行數(shù)據(jù)使用逗號進行分隔

更新時間：2013年04月08日 10:44:48 作者：

sql里將重復行數(shù)據(jù)合并為一行,將多行fieldname字段的內容串聯(lián)起來，用逗號分隔，接下來為大家介紹下詳細的實現(xiàn)sql語句，希望對你有所幫助

一.定義表變量

復制代碼代碼如下:

 
DECLARE @T1 table 
( 
UserID int , 
UserName nvarchar(50), 
CityName nvarchar(50) 
); 

復制代碼代碼如下:

 
insert into @T1 (UserID,UserName,CityName) values (1,'a','上海') 
insert into @T1 (UserID,UserName,CityName) values (2,'b','北京') 
insert into @T1 (UserID,UserName,CityName) values (3,'c','上海') 
insert into @T1 (UserID,UserName,CityName) values (4,'d','北京') 
insert into @T1 (UserID,UserName,CityName) values (5,'e','上海') 

復制代碼代碼如下:

 
select * from @T1 
-----最優(yōu)的方式 
SELECT CityName,STUFF((SELECT ',' + UserName FROM @T1 subTitle WHERE CityName=A.CityName FOR XML PATH('')),1, 1, '') AS A 
FROM @T1 A 
GROUP BY CityName 
----第二種方式 
SELECT B.CityName,LEFT(UserList,LEN(UserList)-1) 
FROM ( 
SELECT CityName,(SELECT UserName+',' FROM @T1 WHERE CityName=A.CityName FOR XML PATH('')) AS UserList 
FROM @T1 A 
GROUP BY CityName 
) B 
stuff(select ',' + fieldname from tablename for xml path('')),1,1,'') 