SQL Server uses SQL statements to merge duplicate row data into one row and separate them with commas (,).

SQL Server uses SQL statements to merge duplicate row data into one row and separate them with commas (,).

1. Define table Variables

The Code is as follows:

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

 

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 ')

Select * from @ T1

----- Optimal Method
SELECT CityName, STUFF (SELECT ',' + UserName FROM @ T1 subTitle WHERE CityName = A. CityName for xml path (''), 1, 1,'') AS
FROM @ T1
Group by CityName

---- Method 2
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
Group by CityName
) B

Stuff (select ',' + fieldname from tablename for xml path (''), 1, 1 ,'')

This entire sentence is used to concatenate the content of multiple fieldname fields and separate them with commas.
For xml path is a method supported by SQL Server 2005 and later versions to generate XML.
The purpose of the stuff function is to remove the comma separator at the top of the string.