I am storing student data by group (or class) and week in the following format:
where GroupData is an array of students (relevant fields are PersonId, StudentName, and GradePoints). GradePoints can change from week to week, so I am trying to come up with a way to display the results by group as follows:
where I can see the grades by week side by side.
The solution I have come up so far is not very elegant:
DECLARE @json VARCHAR(MAX) = (SELECT GroupData FROM Groups WHERE Id = 1) drop table #week1 drop table #week2 SELECT * into #Week1 FROM OPENJSON(@json) WITH ( StudentId INT '$.PersonId', StudentName VARCHAR(100) '$.StudentName', Week1 FLOAT '$.GradePoints' ) DECLARE @json2 VARCHAR(MAX) = (SELECT GroupData FROM Groups WHERE Id = 2) SELECT * into #Week2 FROM OPENJSON(@json2) WITH ( StudentId INT '$.PersonId', StudentName VARCHAR(100) '$.StudentName', Week2 FLOAT '$.GradePoints' ) select w1.studentid, w1.studentname, w1.week1, w2.week2 from #week1 w1 FULL join #week2 w2 on w1.studentid = w2.studentid
Besides not being elegant, this approach is not scalable for a 14-week course.
If anyone can think of a better solution (even if it requires a change in how the data is stored), I am open to suggestions.
Thanks in advance.