SQL Server 2017中如何遍历JSON数组关联行实现动态Pivot？

原Employee表结构如下：

目标动态转置后的效果如下：

SQL Server 2017 原生支持OPENJSONJSON解析函数和STRING_AGG字符串聚合函数，无需额外扩展即可实现需求，完整实现方案如下：

DECLARE @DynamicPivotColumns NVARCHAR(MAX),
        @DynamicSQL NVARCHAR(MAX)

-- 第一步：遍历所有JSON字段提取不重复的属性名，拼接为PIVOT语法要求的列格式
SELECT @DynamicPivotColumns = STRING_AGG(QUOTENAME(j.AttributeName), ', ')
FROM Employee e
CROSS APPLY OPENJSON(e.JsonData) -- JsonData替换为你表中存储JSON数组的实际字段名
WITH (
    AttributeName NVARCHAR(100) '$.AttributeName' -- JSON路径根据实际结构调整
) j
GROUP BY j.AttributeName

-- 第二步：拼接动态Pivot执行语句
SET @DynamicSQL = N'
SELECT *
FROM (
    -- 基础关联逻辑：将每一行的JSON数组拆分为行集，和员工基础字段关联
    SELECT 
        e.EmployeeId,
        e.EmployeeName, -- 可自行补充需要保留的Employee表非JSON字段
        j.AttributeName,
        j.AttributeValue
    FROM Employee e
    CROSS APPLY OPENJSON(e.JsonData)
    WITH (
        AttributeName NVARCHAR(100) ''$.AttributeName'',
        AttributeValue NVARCHAR(MAX) ''$.AttributeValue''
    ) j
) AS SourceData
PIVOT (
    -- 聚合函数，同一员工同一属性只有一个值时用MAX/MIN都可
    MAX(AttributeValue)
    FOR AttributeName IN (' + @DynamicPivotColumns + N')
) AS PivotedResult
'

-- 执行动态SQL得到结果
EXEC sp_executesql @DynamicSQL

注意事项 #

• 如果你的JSON结构不是AttributeName/AttributeValue的键值对格式，只需修改OPENJSON的WITH子句中的JSON路径匹配实际结构即可
• 若部分员工没有某个JSON属性，转置后对应列会自动填充NULL
• 如需增加过滤条件，直接在基础关联查询的FROM子句后加WHERE即可，不影响动态列的生成
• 如果属性值为数字、日期等特定类型，可以在WITH子句中直接指定对应类型，无需统一用NVARCHAR(MAX)

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