How to return JSON objects in tabular format using T-SQL? 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.
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