SQL Server stitching the multiline content of another column of the same ID into one line

For example, there are two columns of data in the table:

ID Name

1 A

1 b

1 c

2 D

2 E

into the following format:

ID Name

1 a,b,c

2 D,e

Data:

if object_id(#表) is  not NULL Drop Table#表SelectDid,name, from#表Order  by didDrop Table#表Select  theNid'AAA'Name1Did into#表Union  AllSelect  the,'BBB',1 Union  AllSelect  the,'CCC',1 Union  AllSelect  the,'DDD',2 Union  AllSelect  -,'Eee',2

The implementation code is as follows:

--recursively computes multiple rows into a single field--Method 1; withx (did, CNT, list, nid, Le) as (SelectDidCount(1) Over(Partition byDID),cast(Name as varchar( -), nid,1  from#表Union  AllSelectX.DID,X.CNT,cast(x.list+','+A.name as varchar( -)), A.nid,x.le+1  from#表 a,xwhereA.did=X.did andA.nid>X.nid)Select *  fromxwhereLe=CNT--Method 2 The intermediate table is not efficientif object_id(#表) is  not NULL Drop Table#结果SelectDidcast(Name as varchar( -)) name into#结果 from#表Order  by didDeclare @dept int ="',@name varchar(Max)="'UpdateaSet @name=  Case  when @dept=Did Then @name+','+nameElsenameEnd,    @dept=Did , name=@name from#结果 aSelectDidMax(name) from#结果Group  by did--Method 3 Using XML is convenient and simple--select ', ' + name from #表 for XML Path (')Selectdid, name=(Stuff((Select ',' +Name from#表whereDid=A.did forXML Path ("')),1,1,"')) from#表 AGroup  byDid

Results:

SQL Server stitching the multiline content of another column of the same ID into one line