Declare @Table1 Table 
(
	SchoolID varchar(100),
	RegNo int ,
	TermCode varchar(10), 
	ERxamCode varchar(10),
	Sub01 float,
	Sub02 float,
	Sub03 float
)

Insert @Table1 
Select 'AB0',111,'T1','E1',11, 12, Null Union all 
Select 'AB0',112,'T1','E1',Null,10,14 


Declare @Table2 Table 
(
	SubCode varchar(10),
	SubjectName varchar(100),
	MinMark float,
	Maxmark float,
	SchoolID varchar(10)
)

Insert @Table2 
Select '01','Eng',10,20,'AB0' union all 
Select '02','Mat',12,20,'AB0' union all 
Select '03','Fre',10,20,'AB0' 

Declare @Table3 TAble 
(
	SchoolID varchar(10),
	SEnglishName  varchar(10),
	sregNo int 
)

Insert @Table3 
Select 'AB0','Joyal',111 union all 
Select 'AB0','Vincent',112



Select b.SchoolID,c.sRegno,b.SubjectName,b.MaxMark,b.MinMark,a.Mark,c.SEnglishName
From 
(
	Select SChoolID,RegNo,Sub01 Mark,'01' as SubCode From @Table1
	Union All 
	Select SChoolID,RegNo,Sub02 Mark,'02' as SubCode From @Table1
	Union All 
	Select SChoolID,RegNo,Sub03 Mark,'03' as SubCode From @Table1
) as A 
Left outer Join @Table2 as B on 
 a.SubCode = b.SubCode
Inner Join @TAble3 as c on 
c.sregNo= a.RegNo


--Output 
SchoolID   sRegno      SubjectName MaxMark     MinMark     Mark        SEnglishName 
---------- ----------- ----------- ----------- ----------- ----------- ------------ 
AB0        111         Eng         20          10          11          Joyal
AB0        111         Mat         20          12          12          Joyal
AB0        111         Fre         20          10          NULL        Joyal
AB0        112         Eng         20          10          NULL        Vincent
AB0        112         Mat         20          12          10          Vincent
AB0        112         Fre         20          10          14          Vincent