declare @gid int
declare @str varchar(500)
declare @num int
set @gid=0
set @num=1
select id,num,space(500) as str,0000 as rownumber into #tt from tablename
update #tt
set str=(case when @gid=id then @str + ',' + convert(varchar,num) else convert(varchar,num) end) ,
@str=(case when @gid=id then @str + ',' + convert(varchar,num) else convert(varchar,num) end),
rownumber=(case when @gid=id then @num+1 else 1 end),
@num=(case when @gid=id then @num+1 else 1 end),
@gid=id
---分组加行号
select id,num,rownumber from #tt
---将多行数据合并为一行
select a.id,a.str from #tt a inner join
(select id,max(rownumber) as ma from #tt group by id) b
on a.id=b.id and a.rownumber=ma