首页 > 代码库 > sql里将重复行数据合并为一行,数据用逗号分隔

sql里将重复行数据合并为一行,数据用逗号分隔

create table aa
  (
  UserID int ,
  UserName nvarchar(50),
  CityName nvarchar(50)
  );
 
 insert into aa (UserID,UserName,CityName) values (1,‘a‘,‘上海‘)
 insert into aa (UserID,UserName,CityName) values (2,‘b‘,‘北京‘)
 insert into aa (UserID,UserName,CityName) values (3,‘c‘,‘上海‘)
 insert into aa (UserID,UserName,CityName) values (4,‘d‘,‘北京‘)
 insert into aa (UserID,UserName,CityName) values (5,‘e‘,‘上海‘)
 
 select * from aa

技术分享
 
 
 SELECT CityName,STUFF((SELECT ‘,‘ + UserName FROM aa subTitle WHERE CityName=A.CityName FOR XML PATH(‘‘)),1, 1, ‘‘) AS A
 FROM aa A
 GROUP BY CityName

技术分享

sql里将重复行数据合并为一行,数据用逗号分隔