高分求助一行列转换SQL语句，急急急！

[scofieldwuwu]

有下面3个表：
CREATE TABLE Class
(
ID INT IDENTITY(1,1) PRIMARY KEY,
ClassName NVARCHAR(20)
)
CREATE TABLE Student
(
ID INT IDENTITY(1,1) PRIMARY KEY,
StudentName NVARCHAR(20),
ClassID INT,
GroupID INT
)

CREATE TABLE StudentGroup
(
ID INT IDENTITY(1,1) PRIMARY KEY,
[Description] NVARCHAR(20)
)

数据和要求如下图：

[--小F--]

select 
      c.classname,
      sum(case when a.groupid=1 then 1  else 0 end) as 洪兴,
      ..
from
       students as a inner join StudentGroup as b on a.GroupID=b.ID
                              inner join Class as c on a.ClassID=c.ID
group by
        c.classname

[--小F--]

省略号的地方自己去补齐啊 一样的格式 就是改成

[--小F--]

sum(case when a.groupid=2 then 1 else 0 end) as 东兴, sum(case when a.groupid=3 then 1 else 0 end) ...

[文盲老顾]

declare @class table(id int identity,className nvarchar(20))
declare @student table(id int identity,sname nvarchar(20),cid int,gid int)
declare @group table(id int identity,gname nvarchar(20))
insert into @class(className) values('班级1'),('班级2'),('班级5')
insert into @group(gname) values('洪兴'),('东兴'),('中兴'),('华为'),('三星')
insert into @student(sname,cid,gid) values('张三',1,1),('李斯',1,1),('李四',1,1),('王五',1,1),('赵六',1,2),('赵一',1,2),('赵二',1,5),('赵三',1,5),('赵四',2,1),('赵七',3,2)
declare @tb table (className nvarchar(20),GroupName nvarchar(20),cnt int)
insert into @tb select a.className,b.gname,(select count(*) from @student where cid=a.id and gid=b.id) as cnt from @class a,@group b order by a.id
declare @cn nvarchar(20),@gn nvarchar(20),@cnt int,@sql nvarchar(max),@ccn nvarchar(20)
declare tb cursor for select className,groupName,cnt from @tb
open tb
set @ccn = ''
set @sql = 'select '
fetch next from tb into @cn,@gn,@cnt
while @@FETCH_STATUS=0
	begin
		if @ccn<>@cn
			begin
				if @ccn<>''
					begin
						set @sql = @sql + ' union select '
					end
				set @ccn = @cn
				set @sql = @sql + '''' + @cn + ''' as 班级'
			end
		set @sql = @sql + ',' + CONVERT(nvarchar,@cnt) + ' as ' + @gn 
		fetch next from tb into @cn,@gn,@cnt
	end
close tb
deallocate tb
--print(@sql)
exec(@sql)

[文盲老顾]

declare @class table(id int identity,className nvarchar(20))
declare @student table(id int identity,sname nvarchar(20),cid int,gid int)
declare @group table(id int identity,gname nvarchar(20))
insert into @class(className) values('班级1'),('班级2'),('班级5')
insert into @group(gname) values('洪兴'),('东兴'),('中兴'),('华为'),('三星')
insert into @student(sname,cid,gid) values('张三',1,1),('李斯',1,1),('李四',1,1),('王五',1,1),('赵六',1,2),('赵一',1,2),('赵二',1,5),('赵三',1,5),('赵四',2,1),('赵七',3,2)
select *
	,(select count(*) from @student where cid=a.id and gid=1) as 洪兴
	,(select count(*) from @student where cid=a.id and gid=2) as 东兴
	,(select count(*) from @student where cid=a.id and gid=3) as 中兴
	,(select count(*) from @student where cid=a.id and gid=4) as 华为
	,(select count(*) from @student where cid=a.id and gid=5) as 三星
from @class a

一句Sql指令的话,那就只能给固定的值了