Merge duplicate rows of data into one row in SQL, concatenate the content of multiple fieldname fields, and separate them with commas. Next we will introduce the detailed implementation of SQL statements for you.
Merge duplicate rows of data into one row in SQL, concatenate the content of multiple fieldname fields, and separate them with commas. Next we will introduce the detailed implementation of SQL statements for you.
1. Define table Variables
The Code is as follows:
DECLARE @ T1 table
(
UserID int,
UserName nvarchar (50 ),
CityName nvarchar (50)
);
The Code is 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 ')
The Code is as follows:
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.
:
