zxca, Sql Server - rextester

Language: Layout:

CREATE TABLE Student (StudentID INT)
CREATE TABLE Grade (GradeID INT)
CREATE TABLE GradeLine (GradeLineID INT, StudentID INT, GradeID INT)

INSERT INTO Student values (1)
INSERT INTO Student values (2)
INSERT INTO Student values (3)
INSERT INTO Student values (4)
INSERT INTO Student values (5)

INSERT INTO Grade values (1)
INSERT INTO Grade values (2)
INSERT INTO Grade values (3)
INSERT INTO Grade values (4)

INSERT INTO GradeLine values (1, 1, 1)
INSERT INTO GradeLine values (2, 1, 2)
INSERT INTO GradeLine values (3, 1, 3)
INSERT INTO GradeLine values (4, 1, 4)
INSERT INTO GradeLine values (5, 1, 2)
INSERT INTO GradeLine values (6, 1, 3)

INSERT INTO GradeLine values (7, 2, 3)
INSERT INTO GradeLine values (8, 2, 3)

INSERT INTO GradeLine values (9, 3, 4)
INSERT INTO GradeLine values (10, 3, 2)
INSERT INTO GradeLine values (11, 3, 3)
INSERT INTO GradeLine values (12, 3, 1)
INSERT INTO GradeLine values (13, 3, 1)
INSERT INTO GradeLine values (14, 3, 1)
INSERT INTO GradeLine values (15, 3, 2)
INSERT INTO GradeLine values (16, 3, 3)
INSERT INTO GradeLine values (17, 3, 4)
INSERT INTO GradeLine values (18, 3, 1)
INSERT INTO GradeLine values (19, 3, 1)
INSERT INTO GradeLine values (20, 3, 1)
INSERT INTO GradeLine values (21, 4, 2)
INSERT INTO GradeLine values (22, 4, 3)
INSERT INTO GradeLine values (23, 4, 3)
INSERT INTO GradeLine values (24, 4, 1)
INSERT INTO GradeLine values (25, 4, 4)
INSERT INTO GradeLine values (26, 4, 1)
INSERT INTO GradeLine values (27, 4, 2)
INSERT INTO GradeLine values (28, 4, 3)
INSERT INTO GradeLine values (29, 4, 1)

SELECT		piv.StudentID,
			piv.[1] AS Distinction,
			piv.[2] AS Merit,
			piv.[3] AS Pass,
			piv.[4] AS Fail,
			(piv.[1] + piv.[2] + piv.[3] + piv.[4]) AS Total

FROM 
(
  SELECT	StudentID, GradeID
  FROM		GradeLine
) src
PIVOT
(
  COUNT(GradeID)
  FOR GradeID IN ([1], [2], [3], [4])
) piv

View schema
Execution time: 0,03 sec, rows selected: 4, rows affected: 38, absolute service time: 0,19 sec