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 -----最优的⽅式
SELECT CityName,STUFF((SELECT ',' + UserName FROM @T1 subTitle WHERE CityName=A.CityName FOR XMLPATH('')),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,'') 这⼀整句的作⽤是将多⾏fieldname字段的内容串联起来，⽤逗号分隔。 for xml path是SQL Server 2005以后版本⽀持的⼀种⽣成XML的⽅式。 stuff函数的作⽤是去掉字符串最前⾯的逗号分隔符。 效果图：