IF OBJECT_ID(N'stuSubject') IS NOT NULL
DROP TABLE stuSubject
GO
create table stuSubject(id int,stusj varchar(10))
INSERT INTO stuSubject select 1,'语文'
INSERT INTO stuSubject select 2,'数学'
INSERT INTO stuSubject select 3,'英语'
IF OBJECT_ID(N'stuName') IS NOT NULL
DROP TABLE stuName
GO
create table stuName(id int,stuname varchar(10))
insert into stuName select 1,'张三'
insert into stuName select 2,'李四'
insert into stuName select 3,'王五'
IF OBJECT_ID(N'stuTest') IS NOT NULL
DROP TABLE stuTest
GO
create table stuTest(id int,stunameId int,stusjId int,stuMark VARCHAR(10))
insert into stuTest select 1,1,1,90
insert into stuTest select 2,1,2,32
insert into stuTest select 3,1,3,43
insert into stuTest select 4,2,1,54
insert into stuTest select 5,2,2,65
insert into stuTest select 6,2,3,93
insert into stuTest select 7,3,1,65
insert into stuTest select 8,3,2,-1
insert into stuTest select 9,3,3,34
go

SELECT 
id,
stuname,
[语文],
[数学],
[英语]

FROM 

(
SELECT 
B.id,
B.stuname,
C.stusj,
AA=CASE A.stuMark WHEN -1 THEN '缺考' ELSE A.stuMark END
FROM stuTest A
JOIN stuname B ON A.stunameId = B.id
JOIN stuSubject C ON A.stusjId = C.id
) P
pivot (
MAX(AA) FOR stusj IN ([语文],[数学],[英语])
)
AS pvt
ORDER BY 1

/*
id          stuname    语文         数学         英语
----------- ---------- ---------- ---------- ----------
1           张三         90         32         43
2           李四         54         65         93
3           王五         65         缺考         34

(3 行受影响)

*/