2023年6月23日发(作者：)

sql⾥将重复⾏数据合并为⼀⾏，数据⽤逗号分隔 1 ⼀.定义表变量 2

3 DECLARE @T1 table 4 ( 5 UserID int ,

6 UserName nvarchar(50), 7 CityName nvarchar(50) 8 ); 9

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

16 select * from @T117

18 -----最优的⽅式19 SELECT CityName,STUFF((SELECT ',' + UserName FROM @T1 subTitle WHERE CityName=me FOR XML PATH('')),1, 1, '') AS A20 FROM @T1 A21 GROUP BY CityName22

23 ----第⼆种⽅式24 SELECT me,LEFT(UserList,LEN(UserList)-1)

25 FROM (26 SELECT CityName,(SELECT UserName+',' FROM @T1 WHERE CityName=me FOR XML PATH('')) AS UserList27 FROM @T1 A

28 GROUP BY CityName29 ) B30

31 stuff(select ',' + fieldname from tablename for xml path('')),1,1,'')32 这⼀整句的作⽤是将多⾏fieldname字段的内容串联起来，⽤逗号分隔。33

34 for xml path是SQL Server 2005以后版本⽀持的⼀种⽣成XML的⽅式。35

36 stuff函数的作⽤是去掉字符串最前⾯的逗号分隔符。