SQL Sever uses SQL statements to merge duplicate row data into one row and separate them with commas

Time:2021-5-11

1、 Define table variables

Copy codeThe code is as follows:
DECLARE @T1 table
(
UserID int ,
UserName nvarchar(50),
CityName nvarchar(50)
);

Insert into @ T1 (user ID, user name, city name) values (1, ‘a’,’shanghai ‘)
Insert into @ T1 (user ID, user name, city name) values (2, ‘B’,’Beijing ‘)
Insert into @ T1 (user ID, user name, city name) values (3, ‘C’,’shanghai ‘)
Insert into @ T1 (user ID, user name, city name) values (4,’d ‘,’Beijing’)
Insert into @ T1 (user ID, user name, city name) values (5, ‘e’,’shanghai ‘)

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

—-The second way
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,”)

The function of this whole sentence is to concatenate the contents of multiple lines of fieldname fields, separated by commas.
For XML path is a way to generate XML supported by SQL server after 2005.
The stuff function removes the comma separator at the beginning of the string.

design sketch: