比如表中有两列数据 :
变成如下格式:
数据:
if object_id(#表)is not null drop table #表
select did,name, from #表 order by did
drop table #表
select 66 nid,'aaa' name,1 did into #表 union all
select 67,'bbb',1 union all
select 80,'ccc',1 union all
select 69,'ddd',2 union all
select 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 all
select x.did,x.cnt,cast(x.list+','+a.name as varchar(100)),a.nid,x.le+1 from #表 a,x
where a.did=x.did and a.nid>x.nid
)
select * from x
where le=cnt
--方法2 中间表效率不好
if object_id(#表)is not null drop table #结果
select did,cast(name as varchar(2000)) name
into #结果
from #表
order by did
declare @dept int ='',@name varchar(max) =''
update a
set @name= case when @dept=did then @name+','+name
else name
end,
@dept=did,
name=@name
from #结果 a
select 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
结果: