0

I am storing student data by group (or class) and week in the following format:

enter image description here

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:

enter image description here

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.

  • Please post queries to create the the table with the relevant indexes and insert sample data. According to the sample data please explain what is the requested result which you want to get – Ronen Ariely Jan 12 at 2:25
0

You don't need to split the data out into separate tables and re-join for this. Simply use OPENJSON to decompose the JSON into a table and use PIVOT to shift the grade point data into columns.

The below example code can be seen in action here.

Setup example data:

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 } ]')

Query the data:

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

Results:

GroupId     StudentName     Week 1  Week 2
------------------------------------------
3840        Barry           100     95
3840        Jenny           100     99
3840        Kelly           98      87
| improve this answer | |
  • Thank you SO much for this tip! It is exactly what I was looking for. – gacanepa Jan 13 at 12:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.