SQL to merge duplicate row data into one row of data using commas to separate _mssql2005

I. Defining table Variables

Copy Code code as follows:

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

Copy Code code as follows:

Insert into @T1 (userid,username,cityname) VALUES (1, ' A ', ' Shanghai ')
Insert into @T1 (Userid,username,cityname) VALUES (2, ' B ', ' Beijing ')
Insert into @T1 (Userid,username,cityname) VALUES (3, ' C ', ' Shanghai ')
Insert into @T1 (userid,username,cityname) VALUES (4, ' d ', ' Beijing ')
Insert into @T1 (userid,username,cityname) VALUES (5, ' e ', ' Shanghai ')

Copy Code code as follows:

SELECT * FROM @T1
-----The best way
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
----Second Way
SELECT B.cityname,left (Userlist,len (userlist)-1)
From (
Select CityName, (select Username+ ', ' from @T1 WHERE to 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, ' "

This whole sentence is used to concatenate the contents of multiple rows of fieldname fields, separated by commas.
For XML path is a way to generate XML that is supported by versions of SQL Server 2005.
The role of the stuff function is to remove the first comma delimiter from the string.
Effect Chart: