SQL 查询 - CROSS APPLY OPENJSON - 如何包含 PIVOT

Question

我是这个主题的新手，需要您的帮助。我正在尝试通过查询解析 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

结果:

<表类=“s-表”><标题>requestIDId数据 <正文>16901111924355169021119242502690121192422726902211924300

目标:

<表类=“s-表”><标题>requestID6901192 <正文>114355124250214227224300

Answer 1

您只需添加 PIVOT 子句:

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;

<小时/>

如果你想动态化，你需要首先将结果物化到临时表中:

DROP TABLE IF EXISTS #DataSource;

WITH request
as
(
SELECT requestId,
       property1191,
       '['+replace(replace(property1191, '[', ''), ']', '')+']' as json
from capex_management_requests
)
SELECT requestId, [690], [1192]
INTO #DataSource
FROM (
    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
) src

DECLARE @DynammicTSQLStatement NVARCHAR(MAX)
       ,@DynamicPIVOTColumns NVARCHAR(MAX);

SELECT @DynamicPIVOTColumns = STRING_AGG(CAST(QUOTENAME([Id]) AS NVARCHAR(MAX)), ',') WITHIN GROUP (ORDER BY [Id])
FROM
(
    SELECT DISTINCT [Id]
    FROM #DataSource
   
) DS ([EVT]);

SET @DynammicTSQLStatement = N'
SELECT *
FROM #DataSource
PIVOT
(
    MAX([data]) FOR [Id] IN (' + @DynamicPIVOTColumns + ')
) PVT';

EXEC sp_executesql @DynammicTSQLStatement;