--> 测试数据: #学生表
if object_id('tempdb.dbo.#学生表') is not null drop table #学生表
create table #学生表 (ID int,姓名 varchar(4))
insert into #学生表
select 1,'张三' union all
select 2,'李四'
--> 测试数据: #课程表
if object_id('tempdb.dbo.#课程表') is not null drop table #课程表
create table #课程表 (ID int,课程 varchar(4))
insert into #课程表
select 1,'数学' union all
select 2,'语文' union all
select 3,'英语'
--> 测试数据: #成绩表
if object_id('tempdb.dbo.#成绩表') is not null drop table #成绩表
create table #成绩表 (学生ID int,课程ID int,成绩 int)
insert into #成绩表
select 1,1,88 union all
select 2,1,77 union all
select 1,2,99 union all
select 1,3,0 union all
select 2,3,35 union all
select 2,2,0
go

declare @sql varchar(8000)
set @sql='select a.姓名'
select @sql=@sql+',max(case when b.课程ID='+ltrim(ID)+' then b.成绩 else 0 end) ['+课程+']'
from #课程表

exec (@sql+' from #学生表 a join #成绩表 b on a.ID=b.学生ID group by a.姓名')

go
drop table #学生表,#课程表,#成绩表

/*
姓名   数学          语文          英语          
---- ----------- ----------- ----------- 
李四   77          0           35
张三   88          99          0
*/

------解决方案--------------------
SQL code

create table xuesheng
(id int,xingming nvarchar(20))

insert into xuesheng
select 1  , '张三'
union select 2,   '李四'

create table kecheng 
(id int, mingcheng nvarchar(20))
insert into kecheng
select 1  , '数学'
union select 2,   '语文'
union select 3,   '英语'

create table chengji
(xueshengid int ,chengjiid int,chengji int)
insert into chengji select 1       ,    1      ,     88 
union select 2     ,      1      ,     77 
union select 1       ,    2      ,     99 
union select 1     ,      3      ,     0 
union select 2      ,     3      ,     35 
union select 2      ,     2      ,     0 

create view chengjidan
as 
select xingming,mingcheng,chengji from
xuesheng X,kecheng K,chengji C
where X.id=C.xueshengid and K.id=C.chengjiid

select xingming,[数学],[语文],[英语] from chengjidan
pivot
(
max(chengji) for mingcheng in ([数学],[语文],[英语]) 
) pvt