Here is something similar that you can study and relate it to your assignment.
SQL PIVOT Rows to Columns with JOIN Tables[
^]
BEGIN
DECLARE @Sport TABLE (
SportId INT,
SportName VARCHAR(50)
)
DECLARE @Game TABLE (
SportId INT,
AthleteId INT,
Scores INT
)
DECLARE @Athlete TABLE (
AthleteId INT,
AthleteName VARCHAR(50)
)
END
BEGIN
INSERT INTO @Athlete
SELECT 1, 'Michael'
UNION
SELECT 2, 'Richard';
END
BEGIN
INSERT INTO @Sport
SELECT 1, 'Ping Pong'
UNION
SELECT 2, 'Basketball'
UNION
SELECT 3, 'Football';
END
BEGIN
INSERT INTO @Game
SELECT 1, 1, 90
UNION
SELECT 2, 1, 67
UNION
SELECT 3, 1, 78
UNION
SELECT 1, 2, 77
UNION
SELECT 2, 2, 87
UNION
SELECT 3, 2, 54;
END
SELECT AthleteName, [Ping Pong], Basketball
FROM
(
SELECT
AthleteName, SportName, Scores
FROM @Game t JOIN @Athlete s
ON t.AthleteId = s.AthleteId
JOIN @Sport sub
ON t.SportId = sub.SportId
WHERE t.AthleteId = 1
)as s
PIVOT
(
AVG(Scores)
FOR SportName IN ([Ping Pong], Basketball, Football)
)as Pivot_alias
Output:
AthleteName Ping Pong Basketball
Michael 90 67