SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tblCourses (
CourseID INT IDENTITY PRIMARY KEY,
CourseName VARCHAR(300)
)

CREATE TABLE tblCourseComponents (
ExamID INT IDENTITY PRIMARY KEY,
ComponentName VARCHAR(300),
CourseID INT -- FOREIGN KEY REFERENCES tblCourses.CourseID
)

INSERT INTO tblCourses (CourseName)
VALUES ('Course A'), ('Course B'), ('Course C');

SELECT * FROM tblCourses

INSERT INTO tblCourseComponents (ComponentName, CourseID)
VALUES ('Course A Exam 1',1),
('Course A Exam 2',1),
('Course B Exam 1',2),
('Course C Exam 1',3),
('Course C Exam 2',3),
('Course C Coursework 1',3);

SELECT * FROM tblCourseComponents

CREATE TABLE tblStudentScoreHistory
(ScoreID INT IDENTITY PRIMARY KEY,
CID VARCHAR(8),
CourseID INT ,
UnitMark DECIMAL (8,6),
ModeratedScore DECIMAL (8,6)
)

INSERT INTO tblStudentScoreHistory (CID, CourseID, UnitMark, ModeratedScore) 
  VALUES
('1',1,1,1.5),
('2',2,2,2.0),
('3',3,3,3.5),
('3',3,3,3.6),
('3',3,3,3.1);

SELECT * FROM   tblStudentScoreHistory

--And a table to record the component marks:
CREATE TABLE tblStudentComponentInformation (
ID INT IDENTITY PRIMARY KEY,
RelatedScoreID INT ,
ExamID INT,
CID VARCHAR(8),
ComponentScorePercent DECIMAL(8,6),
ModeratedComponentScorePercent DECIMAL(8,6)
);

INSERT INTO tblStudentComponentInformation 
(CID, RelatedScoreID, ExamID, ComponentScorePercent, ModeratedComponentScorePercent)
  VALUES
('1',1,1,50,55),
('2',2,2,60,60),
('3',3,3,70,70),
('3',3,3,80,85),
('3',3,3,90,95);

SELECT * FROM tblStudentComponentInformation;

WITH Scores as
(  
SELECT SSH.CID, CC.CourseID, CC.CourseID *10 as CourseID2, ComponentName, ComponentScorePercent, ModeratedComponentScorePercent
FROM tblCourseComponents CC
INNER JOIN tblStudentScoreHistory SSH on SSH.CourseID=CC.CourseID  
INNER JOIN  tblStudentComponentInformation SCI ON SSH.CID= SCI.CID
)
-- Pivot table with one row and five columns  
SELECT CID, [1] AS [Course A Exam 1 unmod mark], [10] AS [Course A Exam 1 mod mark],
  [2]  AS [Course A Exam 2 unmod mark], [20] AS [Course A Exam 2 mod mark], 
  [3] AS [Course B Exam 1 unmod mark], [30] AS [Course B Exam 1 mod mark], 
  [4] AS [Course C Exam 1 unmod mark], [40] AS [Course C Exam 1 mod mark], 
  [5] AS [Course C Exam 2 unmod mark], [50] AS [Course C Exam 2 mod mark],
  [6] AS [Course C Exam 3 unmod mark], [60] AS [Course C Exam 3 mod mark]
FROM  
Scores
PIVOT  -- For ComponentScorePercent AVG
(  
  AVG(ComponentScorePercent)  
  FOR CourseID IN ([1], [2], [3], [4], [5], [6])  
) AS Pv1
PIVOT  -- For ModeratedComponentScorePercent AVG
(
   AVG(ModeratedComponentScorePercent)
   FOR CourseID2 IN  ([10], [20], [30], [40], [50], [60])  
) AS pv2

CourseID	CourseName
1	Course A
2	Course B
3	Course C

ExamID	ComponentName	CourseID
1	Course A Exam 1	1
2	Course A Exam 2	1
3	Course B Exam 1	2
4	Course C Exam 1	3
5	Course C Exam 2	3
6	Course C Coursework 1	3

ScoreID	CID	CourseID	UnitMark	ModeratedScore
1	1	1	1.000000	1.500000
2	2	2	2.000000	2.000000
3	3	3	3.000000	3.500000
4	3	3	3.000000	3.600000
5	3	3	3.000000	3.100000

ID	RelatedScoreID	ExamID	CID	ComponentScorePercent	ModeratedComponentScorePercent
1	1	1	1	50.000000	55.000000
2	2	2	2	60.000000	60.000000
3	3	3	3	70.000000	70.000000
4	3	3	3	80.000000	85.000000
5	3	3	3	90.000000	95.000000

CID	Course A Exam 1 unmod mark	Course A Exam 1 mod mark	Course A Exam 2 unmod mark	Course A Exam 2 mod mark	Course B Exam 1 unmod mark	Course B Exam 1 mod mark	Course C Exam 1 unmod mark	Course C Exam 1 mod mark	Course C Exam 2 unmod mark	Course C Exam 2 mod mark	Course C Exam 3 unmod mark	Course C Exam 3 mod mark
1	50.000000	55.000000	null	null	null	null	null	null	null	null	null	null
1	50.000000	55.000000	null	null	null	null	null	null	null	null	null	null
2	null	null	60.000000	60.000000	null	null	null	null	null	null	null	null
3	null	null	null	null	70.000000	70.000000	null	null	null	null	null	null
3	null	null	null	null	80.000000	85.000000	null	null	null	null	null	null
3	null	null	null	null	90.000000	95.000000	null	null	null	null	null	null
3	null	null	null	null	70.000000	70.000000	null	null	null	null	null	null
3	null	null	null	null	80.000000	85.000000	null	null	null	null	null	null
3	null	null	null	null	90.000000	95.000000	null	null	null	null	null	null
3	null	null	null	null	70.000000	70.000000	null	null	null	null	null	null
3	null	null	null	null	80.000000	85.000000	null	null	null	null	null	null
3	null	null	null	null	90.000000	95.000000	null	null	null	null	null	null