交叉应用openjson / pivot -多条目

Question

下面的代码，由Charlieface在这个答案中编写，转换并打开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 CAST(arr.[key] AS int)) AS row_id
FROM request P
CROSS APPLY OPENJSON (P.json) AS arr
CROSS APPLY OPENJSON (arr.value)
WITH 
(
Id VARCHAR(200) N'$.metaId',
data VARCHAR(200)
) AS AttsData
) DS
PIVOT 
(
MAX(data) FOR Id IN ([690], [1192])
) piv;

仅限当前结果"MAX"每个ID的值[690]value

<表类> requestId row_id 690 1192 tbody><<tr>1x143521x238871x343721x437491x5136931x894228

如果我理解这个问题，我相信您会避免PIVOT以避免MAX聚合。你可以用CASE语句来找出主元应该有的值。我把子查询作为另一个CTE，也许是为了更清楚地了解发生了什么。

WITH request
as
(
SELECT requestId,
property1191,
'['+replace(replace(property1191, '[', ''), ']', '')+']' as json
from capex_management_requests
), 
ds AS (
SELECT
P.requestId,
AttsData.[Id],
AttsData.[data],
ROW_NUMBER() OVER (PARTITION BY P.requestId, AttsData.[Id]
ORDER BY CAST(arr.[key] AS int)) AS row_id
FROM request P
CROSS APPLY OPENJSON (P.json) AS arr
CROSS APPLY OPENJSON (arr.value)
WITH 
(
Id VARCHAR(200) N'$.metaId',
data VARCHAR(200)
) AS AttsData
)
SELECT 
requestId, 
data,
row_id,
CASE WHEN Id = '690' THEN data ELSE NULL END AS [690],
CASE WHEN Id = '1192' THEN data ELSE NULL END AS [1192]
FROM ds