select @sql='',@i=aa,@im=cast(aa-1 as varchar)
from(select top 1 Sibling,count(Sibling) as aa
from #tb group by Sibling order by count(Sibling) desc) a
while @i>0
select @sql=char(13)+',max(case id % '+@im+' when '+cast(@i-1 as varchar)
+' then name end) as [name'+cast(@i as varchar)+']'+@sql
,@i=@i-1
set @sql='select Sibling'+char(13)+@sql
+char(13)+'from #tb group by Sibling'
--1.创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(SiblingGroupID as varchar) from 学生表 where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
go
--调用自定义函数得到结果
select distinct 学号,dbo.fmerg(SiblingGroupID) from 学生表