SQL Sever中使用SQL语句实现把重复行数据合并为一行并用逗号分隔

前端技术 2023/09/03 MSSQL

一.定义表变量

复制代码 代码如下:

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

-----最优的方式
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,\'\')

本文地址：https://www.stayed.cn/item/8575

转载请注明出处。

本站部分内容来源于网络,如侵犯到您的权益,请 联系我

我的博客

人生若只如初见，何事秋风悲画扇。

我的标签

随笔档案