如何将CROSS APPLY OPENJSON与PIVOT集成实现JSON数据透视

我是该领域的新手，正尝试通过查询解析JSON字符串，现在需要对数据进行透视操作，但不清楚如何集成PIVOT命令。以下是我的代码、当前查询结果及目标数据格式：

代码 #

WITH request
as
(
SELECT requestId,
       property1191,
       '['+replace(replace(property1191, '[', ''), ']', '')+']' as json
from capex_management_requests
)
SELECT P.requestId, property1191, AttsData.[Id], AttsData.[data]
FROM request P CROSS APPLY OPENJSON (P.json, N'$') WITH (
    Id VARCHAR(200) N'$.metaId',
    data VARCHAR(200) N'$.data'
  ) AS AttsData

当前结果 #

目标格式 #

解决方案 #

可以在基础查询中添加分组序号，再结合PIVOT命令实现目标格式。修改后的SQL如下：

WITH request AS (
    SELECT requestId,
           '['+replace(replace(property1191, '[', ''), ']', '')+']' as json
    from capex_management_requests
),
base_data AS (
    SELECT 
        P.requestId, 
        AttsData.[Id], 
        AttsData.[data],
        -- 为每个requestId下的同类型Id数据分配分组序号，确保配对
        ROW_NUMBER() OVER (PARTITION BY P.requestId, AttsData.[Id] ORDER BY AttsData.[data]) AS group_seq
    FROM request P 
    CROSS APPLY OPENJSON (P.json, N'$') WITH (
        Id VARCHAR(200) N'$.metaId',
        data VARCHAR(200) N'$.data'
    ) AS AttsData
)
-- 执行透视，将Id转为列名
SELECT requestId, [690], [1192]
FROM base_data
PIVOT (
    MAX(data) -- 每组内对应Id仅一个值，MAX/MIN均可
    FOR Id IN ([690], [1192])
) AS pivot_result
ORDER BY requestId, group_seq;

说明 #

1. 新增的group_seq用于区分同一requestId下的不同数据组，确保690和1192的对应数据能正确配对；
2. PIVOT中指定需要转为列的Id值（690、1192），如果后续有更多Id值，可改为动态SQL实现；
3. 最终结果会按requestId和分组序号排序，与目标格式一致。

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