如何使用 T-SQL 以表格格式返回 JSON 對象？

我按以下格式按組（或班級）和周儲存學生數據：

其中GroupData是一組學生（相關欄位是PersonId、StudentName和GradePoints）。GradePoints可以每週更改，所以我試圖想出一種按組顯示結果的方法，如下所示：

我可以並排查看每週的成績。

到目前為止我提出的解決方案不是很優雅：

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

除了不優雅之外，這種方法對於 14 週的課程也無法擴展。

如果有人能想到更好的解決方案（即使它需要改變數據的儲存方式），我願意接受建議。

提前致謝。

您無需將數據拆分為單獨的表並為此重新加入。只需使用 OPENJSON 將 JSON 分解為表格，並使用 PIVOT 將成績點數據轉換為列。

下面的範常式式碼可以在這裡看到。

設置範例數據：

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

查詢數據：

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

引用自：https://dba.stackexchange.com/questions/257133