Pivot with 3 tables in SQL Server

Hi hazel14,

Refer the below query.

SQL

CREATE TABLE #Students (
    MatricNo INT PRIMARY KEY,
    Name VARCHAR(100),
    Final1 INT,
    Grade varchar(2)
) 
INSERT INTO #Students VALUES(888999, 'Hazel', 221, 'F')
 
CREATE TABLE #Marks(
    MatricNo INT,
    Semester INT,
    Test VARCHAR(50),
    Marks INT
) 
INSERT INTO #Marks VALUES(888999, 1, '1', 15)
INSERT INTO #Marks VALUES(888999, 1, '2', 10)
INSERT INTO #Marks VALUES(888999, 1, '3', 10)
INSERT INTO #Marks VALUES(888999, 1, '4', 20) 
INSERT INTO #Marks VALUES(888999, 2, '1', 25)
INSERT INTO #Marks VALUES(888999, 2, '2', 20)
INSERT INTO #Marks VALUES(888999, 2, '3', 30)
 
CREATE TABLE #Test(
    TestID INT,
    TestName VARCHAR(40)
) 
INSERT INTO #Test VALUES(1, 'Assignment_1')
INSERT INTO #Test VALUES(2, 'Assignment_2')
INSERT INTO #Test VALUES(3, 'Presentation')
INSERT INTO #Test VALUES(4, 'Project')

DECLARE @ColumnName AS NVARCHAR(MAX), @ColumnForSum AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
SELECT @ColumnName = STUFF((SELECT DISTINCT ',' + QUOTENAME(TestName) FROM #Test FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'),1,1,'')

SELECT @ColumnForSum = REPLACE(@ColumnName,',','+')
SELECT @ColumnForSum = REPLACE(@ColumnForSum,'[','ISNULL([')
SELECT @ColumnForSum = REPLACE(@ColumnForSum,']','],0)')
 
set @query = ';WITH CTE AS(
                    SELECT m.MatricNo, s.Name, s.Grade, t.TestName, m.Semester, Marks
					FROM #Marks m
					INNER JOIN #Students s ON m.MatricNo = s.MatricNo
					INNER JOIN #Test t ON m.Test = t.TestID
					GROUP BY m.MatricNo, s.Name, s.Grade, t.TestName, m.Semester, Marks
                )
                SELECT MatricNo, Name, ' + @ColumnName + ', ' + @ColumnForSum + ' Final, Grade, 0 Point FROM CTE    
                PIVOT(MAX(Marks) FOR TestName IN (' + @ColumnName + ')) p '  

EXECUTE(@query);

DROP TABLE #Marks
DROP TABLE #Students
DROP TABLE #Test

Screenshot