IF OBJECT_ID('tempdb..#Parts', 'U') IS NOT NULL DROP TABLE #Parts
CREATE TABLE #Parts
(
  ID int,
  ID2 int,
  PartNo varchar(10)
)
SET NOCOUNT ON
INSERT INTO #Parts (ID, ID2, PartNo) Values (75, 23921, 'DENT')
INSERT INTO #Parts (ID, ID2, PartNo) Values (75, 26145, 'PLGD')
INSERT INTO #Parts (ID, ID2, PartNo) Values (75, 26145, 'PRRP')
INSERT INTO #Parts (ID, ID2, PartNo) Values (75, 26145, 'SIST')
INSERT INTO #Parts (ID, ID2, PartNo) Values (75, 57290, 'PRMD')
INSERT INTO #Parts (ID, ID2, PartNo) Values (75, 57290, 'abc')
INSERT INTO #Parts (ID, ID2, PartNo) Values (75, 57290, 'def')
INSERT INTO #Parts (ID, ID2, PartNo) Values (75, 57290, 'ghi')
INSERT INTO #Parts (ID, ID2, PartNo) Values (75, 57290, 'jkl')
SET NOCOUNT OFF
 
SELECT ID, ID2,
Max((Case When RowNumber = 1 Then PartNo Else '' End)) AS Part1,
Max((Case When RowNumber = 2 Then PartNo Else '' End)) AS Part2,
Max((Case When RowNumber = 3 Then PartNo Else '' End)) AS Part3,
Max((Case When RowNumber = 4 Then PartNo Else '' End)) AS Part4
FROM
(
  SELECT ID, ID2, PartNo,
  ROW_NUMBER() OVER(PARTITION BY ID, ID2 ORDER BY PartNo ASC) AS RowNumber
  FROM #Parts
) AS tbl
GROUP BY ID, ID2

ID          ID2         Part1      Part2      Part3      Part4
----------- ----------- ---------- ---------- ---------- ----------
75          23921       DENT                             
75          26145       PLGD       PRRP       SIST       
75          57290       abc        def        ghi        jkl
 
(3 row(s) affected)

SELECT     Student_1.student_id, SUM(Marks.marks_value) * Hurdle.hurdle_weight / 100 AS total
FROM         Marks INNER JOIN
                      Student AS Student_1 ON Marks.student_id = Student_1.student_id INNER JOIN
                      Class AS Class_1 ON Student_1.student_id = Class_1.student_id INNER JOIN
                      MarkingCriteria ON Marks.criteria_id = MarkingCriteria.criteria_id INNER JOIN
                      AssessmentComponent ON MarkingCriteria.component_id = AssessmentComponent.component_id INNER JOIN
                      Hurdle ON AssessmentComponent.hurdle_id = Hurdle.hurdle_id
GROUP BY Hurdle.hurdle_id, Hurdle.hurdle_weight, Student_1.student_id

SELECT    student_name, student_number, student_email, 
   Max((CASE WHEN RowNumber = 1 THEN total ELSE '' END)) AS Hurdle1, 
   Max((CASE WHEN RowNumber = 2 THEN total ELSE '' END)) AS Hurdle2, 
   Max((CASE WHEN RowNumber = 3 THEN total ELSE '' END)) AS Hurdle3, 
   Max((CASE WHEN RowNumber = 4 THEN total ELSE '' END)) AS Hurdle4, 
   Max((CASE WHEN RowNumber = 5 THEN total ELSE '' END)) AS Hurdle5
 
FROM    (SELECT     Student.student_id, Student.student_name, Student.student_number, Student.student_email, 
   ROW_NUMBER() OVER (PARTITION BY Student.student_id ORDER BY Student.student_id) AS RowNumber,   
   SUM(Marks.marks_value) * Hurdle.hurdle_weight / 100 AS total
   FROM         Marks INNER JOIN
                      Student ON Marks.student_id = Student.student_id INNER JOIN
                      Class ON Student.student_id = Class.student_id INNER JOIN
                      MarkingCriteria ON Marks.criteria_id = MarkingCriteria.criteria_id INNER JOIN
                      AssessmentComponent ON MarkingCriteria.component_id = AssessmentComponent.component_id INNER JOIN
                      Hurdle ON AssessmentComponent.hurdle_id = Hurdle.hurdle_id
   GROUP BY Hurdle.hurdle_id, Hurdle.hurdle_weight, Student.student_id, Student.student_name, Student.student_number,   
   Student.student_email) AS derive
 
GROUP BY student_id, student_name, student_number, student_email