SQL combines repeating rows of data into one row with data separated by commas

1 I. Defining table Variables2 3 DECLARE @T1 Table4 (5Useridint , 6UserNamenvarchar( -),7CityNamenvarchar( -)8 );9 Ten Insert  into @T1(Userid,username,cityname)Values(1,'a','Shanghai') One Insert  into @T1(Userid,username,cityname)Values(2,'b','Beijing') A 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  - Select *  from @T1 -  - -----The best way + SELECTCityName,STUFF((SELECT ',' +UserName from @T1SubTitleWHERECityName=A.cityname forXML PATH ("')),1,1,"') asA -  from @T1A + GROUP  byCityName A  at ----The second way - SELECTB.cityname, Left(UserList,LEN(userlist)-1)  -  from ( -   SELECTCityName, (SELECTUserName+','  from @T1 WHERECityName=A.cityname forXML PATH ("')) asuserlist -    from @T1A -   GROUP  byCityName in ) B -  to Stuff(Select ',' +FieldName fromTableName forXML Path ("')),1,1,"') + The function of this whole sentence is to concatenate the contents of a multiline FieldName field, separated by commas.  -  the  forXML 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 comma delimiter from the front of the string.

SQL combines repeating rows of data into one row with data separated by commas