SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE DataTable
(
  Id INT,
  GroupId INT,
  Week VARCHAR(25),
  GroupData VARCHAR(MAX)
)
INSERT INTO DataTable
VALUES (1, 3840, 'Week 1', '[ { "StudentName": "Barry", "GradePoints": 100 }, { "StudentName": "Kelly", "GradePoints": 98 }, { "StudentName": "Jenny", "GradePoints": 100 } ]'),
  (2, 3840, 'Week 2', '[ { "StudentName": "Barry", "GradePoints": 95 }, { "StudentName": "Kelly", "GradePoints": 87 }, { "StudentName": "Jenny", "GradePoints": 99 } ]')
2 rows affected

SELECT
 GroupId, StudentName, p.[Week 1], p.[Week 2]
FROM
(
 SELECT GroupId, Week, StudentName, GradePoints
 FROM DataTable
 CROSS APPLY OPENJSON ([GroupData])
 WITH (
  StudentName VARCHAR(25) '$.StudentName',
  GradePoints VARCHAR(25) '$.GradePoints'
 )
) src
PIVOT
(
  MAX(GradePoints) FOR [Week] IN ([Week 1], [Week 2])
) p


GroupId
StudentName
Week 1
Week 2


3840
Barry
100
95

3840
Jenny
100
99

3840
Kelly
98
87