下面的代码打开一个json内容，并在最后对其进行透视.不知何故，排序/旋转搞错了.
WITH request
as
(
SELECT requestId,
property1191,
'['+replace(replace(property1191, '[', ''), ']', '')+']' as json
from capex_management_requests
)
SELECT *
FROM
(
SELECT
P.requestId,
AttsData.[Id],
AttsData.[data],
ROW_NUMBER() OVER (PARTITION BY P.requestId, AttsData.[Id]
ORDER BY (SELECT 1) ) AS row_id
FROM request P
CROSS APPLY OPENJSON (P.json, N'$')
WITH
(
Id VARCHAR(200) N'$.metaId',
data VARCHAR(200) N'$.data'
) AS AttsData
) DS
PIVOT
(
MAX(data) FOR Id IN ([690], [1192])
) piv;
JSON example个
{"metaId":690,"data":"1"},{"metaId":1192,"data":"4352"}\],\[{"metaId":690,"data":"2"},{"metaId":1192,"data":"3887"}\],\[{"metaId":690,"data":"3"},{"metaId":1192,"data":"4372"}\],\[{"metaId":690,"data":"4"},{"metaId":1192,"data":"3749"}\],\[{"metaId":690,"data":"51"},{"metaId":1192,"data":"3693"}\],\[{"metaId":690,"data":"51"},{"metaId":1192,"data":"3712"}\],\[{"metaId":690,"data":"89"},{"metaId":1192,"data":"4228"}
Current results - wrong ordering(例如，690:1应显示1192:4352)
| requestId | row_id | 690 | 1192 |
|---|---|---|---|
| 1 | 7 | 1 | 4228 |
| 1 | 6 | 2 | 3712 |
| 1 | 5 | 3 | 3693 |
| 1 | 4 | 4 | 3749 |
| 1 | 2 | 51 | 3887 |
| 1 | 3 | 51 | 4372 |
| 1 | 1 | 89 | 4352 |
Target - correct ordering(未显示row_id)
| requestId | row_id | 690 | 1192 |
|---|---|---|---|
| 1 | x | 1 | 4352 |
| 1 | x | 2 | 3887 |
| 1 | x | 3 | 4372 |
| 1 | x | 4 | 3749 |
| 1 | x | 51 | 3693 |
| 1 | x | 51 | 3712 |
| 1 | x | 89 | 4228 |
我对根据JSON语法的正确排序感兴趣