如何将Cross Apply调用OPENJSON返回的键值对结果转为多列展示

你需要实现的是行转列操作，在SQL Server中可以通过PIVOT关键字实现，分两种场景处理：

场景1：JSON的Key值固定可穷举 #

直接使用静态PIVOT即可，示例代码如下：

SELECT 
    ID,
    [name], [age], [address] -- 替换为实际业务中的所有Key名称
FROM (
    -- 你原有拆解JSON的查询作为子查询
    SELECT 
        [mytable].ID,
        jsonvalues.[Key], 
        jsonvalues.[Value]
    FROM [mytable]
    CROSS APPLY OPENJSON([mytable].[IndexFields])
    WITH (
        [Key] nvarchar(255),
        [Value] nvarchar(255) 
    ) AS jsonValues
) AS src
PIVOT (
    -- 同一ID+Key组合仅单值时，MAX/MIN都不影响结果
    MAX(Value)
    -- 替换为实际业务中的所有Key名称
    FOR [Key] IN ([name], [age], [address])
) AS pvt
ORDER BY ID

场景2：JSON的Key值不固定、会动态新增 #

需要通过动态SQL自动生成列名，避免新增Key时频繁修改查询语句，示例代码如下：

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX)

-- 先查询所有去重的Key，拼接为PIVOT需要的列格式
SELECT @cols = STRING_AGG(QUOTENAME([Key]), ',')
FROM (
    SELECT DISTINCT jsonvalues.[Key]
    FROM [mytable]
    CROSS APPLY OPENJSON([mytable].[IndexFields])
    WITH (
        [Key] nvarchar(255),
        [Value] nvarchar(255) 
    ) AS jsonValues
) AS distinct_keys

-- 拼接完整的动态SQL语句
SET @sql = N'
SELECT ID, ' + @cols + N'
FROM (
    SELECT 
        [mytable].ID,
        jsonvalues.[Key], 
        jsonvalues.[Value]
    FROM [mytable]
    CROSS APPLY OPENJSON([mytable].[IndexFields])
    WITH (
        [Key] nvarchar(255),
        [Value] nvarchar(255) 
    ) AS jsonValues
) AS src
PIVOT (
    MAX(Value)
    FOR [Key] IN (' + @cols + N')
) AS pvt
ORDER BY ID
'

-- 执行动态SQL
EXEC sp_executesql @sql

注意事项 #

• 若同一个ID下同一Key存在多个返回值，可根据需求将聚合函数MAX替换为STRING_AGG(Value, ',')实现多值拼接（仅SQL Server 2017及更高版本支持）
• 若不需要保留原始IndexFields字段，可直接在子查询中移除该字段，降低查询开销。

内容的提问来源于stack exchange，提问作者Nelson André