Select S.StudentID, IsNull(C1.通信原理,0) AS '通信原理', IsNull(C2.编译原理,0) AS '编译原理', IsNull(C3.人工智能,0) AS '人工智能', IsNull(C4.软件工程,0) AS '软件工程'
FROM (
Select DISTINCT StudentID from YourTable) S
LEFT JOIN (
Select StudentID,Score AS '通信原理' from YourTable Where CourseName = '通信原理') C1
ON S.StudentID = C1.StudentID
LEFT JOIN (
Select StudentID,Score AS '编译原理' from YourTable Where CourseName = '编译原理') C2
ON S.StudentID = C2.StudentID
LEFT JOIN (
Select StudentID,Score AS '人工智能' from YourTable Where CourseName = '人工智能') C3
ON S.StudentID = C3.StudentID
LEFT JOIN (
Select StudentID,Score AS '软件工程' from YourTable Where CourseName = '软件工程') C4
ON S.StudentID = C4.StudentID
CREATE PROCEDURE getCrossTableOfA AS
Begin
declare @sql varchar(8000);
set @sql =' select region,';
select @sql = @sql + ' sum(case when [course_name]=''' + [course_name] +''' then num else 0 end ) as ''' +[name] +''', '
from (select distinct [course] from a ) as AA
set @sql = left(@sql,len(@sql)-1) +' from a group by region';
exec(@sql)