create table #leaguer
(
学生编号 int foreign key references dbo.#student([学生编号]) on delete cascade on update cascade,
社团编号 int foreign key references dbo.#league([社团编号]) on delete cascade on update cascade primary key (学生编号,社团编号)
)
insert into #student values('lxl')
insert into #student values('abc')
insert into #student values('88')
insert into #student values('99')
insert into #league values('体育协会')
insert into #league values('音乐协会')
insert into #league values('美术协会')
insert into #league values('科技协会')
insert into #league values('英语协会')
insert into #league values('计算机协会')
insert into #leaguer values(1,4)
insert into #leaguer values(3,2)
insert into #leaguer values(3,5)
insert into #leaguer values(1,2)
insert into #leaguer values(3,4)
insert into #leaguer values(1,3)
insert into #leaguer values(3,1)
insert into #leaguer values(2,1)
insert into #leaguer values(2,5)
declare @sql varchar(8000),@i int,@sql2 varchar(8000)
select @i=0,@sql = 'select 姓名',@sql2=''
select @i=@i+1,@sql = @sql + ',max(case 社团编号 when '''+cast(社团编号 as varchar(10))+''' then 社团名称 end) [社团名称'+cast(@i as varchar(10))+']'
,@sql2=@sql2+',cast(count(社团名称'+cast(@i as varchar(10))+') as varchar(100))'
from #league
select @sql = @sql+' into #a from (select a.姓名,b.社团编号,b.社团名称 from #student a,#league b,#leaguer c where a.学生编号=c.学生编号 and b.社团编号=c.社团编号) tem group by 姓名'
exec(@sql+' select * from #a union all select ''合计'''+@sql2+' from #a')
declare @sql varchar(8000)
set @sql = 'select 部门'
select @sql = @sql + ',sum(case 学历 when '''+学历+''' then 1 else 0 end) ['+学历+'的人数]'
from (select distinct 学历 from 你的表) a