I have two tables with related data regarding courses, and the exams and coursework which make up those courses. The tables are called tblCourses and tblCourseComponents, and are structured as follows:

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
)

One course may have 1 or more components – currently no more than 4 components, but that may change.

Data might look like this:

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

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)

Students take the exams / coursework as specified in tblCourseComponents, and achieve a mark for each of these components. Similarly, they achieve a mark for the course as a whole, which although theoretically is the weighted mean of the component marks, it can be varied, and so is stored separately. Each student is identified by a unique Candidate ID (CID).

So, there’s a table to record the Course marks:

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

And a table to record the component marks:

CREATE TABLE tblStudentComponentInformation (
ID INT IDENTITY PRIMARY KEY,
RelatedScoreID INT FOREIGN KEY REFERENCES tblStudentScoreHistory,
ExamID INT FOREIGN KEY REFERENCES tblCourseComponents (lngExamID),
CID VARCHAR(8),
ComponentScorePercent DECIMAL(8,6),
ModeratedComponentScorePercent DECIMAL(8,6)
)

Note that both the above tables include two marks per student – a mark and a “moderated” mark, which is one which has been adjusted by the board of examiners to take account of extenuating circumstances. Both columns will always be populated, but in most cases, the moderated mark is identical to the unmoderated mark.

I have been asked to produce a report of the data. The idea is that a course code is provided, and the output should have a column for each component’s mark and unmoderated mark (so if there are 3 components, that would be 6 columns, but if there’s only one component, that’s two columns) plus a column for the course unmoderated mark and one for the course moderated mark.

My original thought was to use a I’ve been playing around with Pivots, and with CASE statements, but can’t figure out how to do this.

Grateful for any help.