适配多条目场景：Cross Apply OpenJSON/Pivot代码修改咨询

使用一段基于CROSS APPLY OPENJSON解析JSON并执行Pivot的SQL代码时，发现每个ID[690]仅保留了MAX值，无法展示该ID对应的所有多条目数据，需修改代码以实现目标结果。

原代码 #

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;

当前结果（仅保留每个ID[690]的MAX值） #

目标结果（展示ID[690]的所有多条目） #

修改方案 #

原代码的问题在于ROW_NUMBER()的分区逻辑和Pivot中的MAX(data)聚合函数，导致同一requestId+Id下的多条目被合并。以下两种方案可解决该问题：

方案一：调整Pivot分组键（保留Pivot写法） #

修改ROW_NUMBER()的分区规则，让每个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],
      -- 按requestId和JSON数组元素索引分区，确保每组条目独立
      ROW_NUMBER() OVER (PARTITION BY P.requestId, arr.[key]
                     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 
(
     -- 每个分组内仅一条对应Id的数据，MAX等价于直接取值
     MAX(data) FOR Id IN ([690], [1192])
) piv;

方案二：分数据集交叉连接（直观展示所有组合） #

若需展示同一requestId下690与1192的所有条目组合，可分别提取两个ID的数据集后交叉连接：

WITH request
as
(
    SELECT requestId,
           '['+replace(replace(property1191, '[', ''), ']', '')+']' as json
    from capex_management_requests
),
data_690 AS (
    SELECT 
        requestId,
        data AS [690],
        ROW_NUMBER() OVER (PARTITION BY requestId ORDER BY CAST(arr.[key] AS int)) AS rn
    FROM request
    CROSS APPLY OPENJSON(json) AS arr
    CROSS APPLY OPENJSON(arr.value)
    WITH (
        Id VARCHAR(200) N'$.metaId',
        data VARCHAR(200)
    ) AS atts
    WHERE atts.Id = '690'
),
data_1192 AS (
    SELECT 
        requestId,
        data AS [1192],
        ROW_NUMBER() OVER (PARTITION BY requestId ORDER BY CAST(arr.[key] AS int)) AS rn
    FROM request
    CROSS APPLY OPENJSON(json) AS arr
    CROSS APPLY OPENJSON(arr.value)
    WITH (
        Id VARCHAR(200) N'$.metaId',
        data VARCHAR(200)
    ) AS atts
    WHERE atts.Id = '1192'
)
SELECT 
    d690.requestId,
    ROW_NUMBER() OVER (PARTITION BY d690.requestId ORDER BY d690.rn, d1192.rn) AS row_id,
    d690.[690],
    d1192.[1192]
FROM data_690 d690
JOIN data_1192 d1192 ON d690.requestId = d1192.requestId
ORDER BY d690.requestId, row_id;

说明：

• 方案一适合JSON中每个数组元素包含一组690和1192数据的场景，确保每组数据展示在同一行。
• 方案二会生成690与1192的所有组合（笛卡尔积），与目标结果结构完全匹配。

内容的提问来源于stack exchange，提问作者user21533096