if object_id(#表)is not null drop table #表select did,name, from #表 order by diddrop table #表select 66 nid,‘aaa‘ name,1 did into #表 union allselect 67,‘bbb‘,1 union allselect 80,‘ccc‘,1 union allselect 69,‘ddd‘,2 union allselect 70,‘eee‘,2

--递归计算多行合并成一个字段--方法1;with x (did, cnt, list, nid, le)as (select did,count(1)over(partition by did),cast(name as varchar(100)),nid,1 from #表union allselect x.did,x.cnt,cast(x.list+‘,‘+a.name as varchar(100)),a.nid,x.le+1 from #表 a,xwhere a.did=x.did and a.nid>x.nid)select * from  xwhere le=cnt--方法2 中间表效率不好if object_id(#表)is not null drop table #结果select did,cast(name as varchar(2000)) name into #结果from #表 order by diddeclare @dept int =‘‘,@name varchar(max) =‘‘update aset @name= case when @dept=did then @name+‘,‘+name            else name            end,    @dept=did,    name=@namefrom #结果 aselect did,max(name) from #结果group by did--方法3 使用xml方便，简单--select ‘,‘ + name from #表   for xml path(‘‘)select did, name = (stuff((select ‘,‘ + name from #表 where did =   a.did for xml path(‘‘)),1,1,‘‘)) from #表 a group by did