SQL Server中正确使用JSON_QUERY()结合IN运算符的方法

SQL Server的PIVOT运算符中，IN子句要求明确指定要转置的列名，不能直接使用JSON_QUERY()这类函数动态生成列名，这是语法规则限制，所以你的写法会触发错误。

先正确解析JSON结构，提取出需要的键值对后再执行PIVOT操作，以下是修正后的代码：

DECLARE @DataJson NVARCHAR(MAX) = '{"widgets":{"key1":{"value":[{"text":"TEXT"}]},"key2":{"value":[{"text":"TEXT"}]}}}'

SELECT 
    [key1], [key2]
FROM (
    -- 先解析widgets下的所有键值对，再提取每个value中的text内容
    SELECT 
        WC.[key],
        JSON_VALUE(WC.[value], '$.value[0].text') AS WidgetText
    FROM OPENJSON(@DataJson, '$.widgets') AS WC
) AS SourceData
PIVOT (
    MAX(WidgetText) -- 聚合函数，这里用MAX因为每个key对应唯一值
    FOR [key] IN ([key1], [key2]) -- 明确列出要转置的列名
) AS PivotTable

如果你的JSON中widgets下的key是动态变化的，无法提前列出来，可以用动态SQL生成IN子句：

DECLARE @DataJson NVARCHAR(MAX) = '{"widgets":{"key1":{"value":[{"text":"TEXT"}]},"key2":{"value":[{"text":"TEXT"}]}}}'
DECLARE @PivotColumns NVARCHAR(MAX)

-- 先提取所有widgets的key，拼接成列名字符串
SELECT @PivotColumns = STRING_AGG(QUOTENAME([key]), ', ')
FROM OPENJSON(@DataJson, '$.widgets')

-- 动态生成并执行PIVOT语句
DECLARE @DynamicSQL NVARCHAR(MAX) = N'
SELECT *
FROM (
    SELECT 
        WC.[key],
        JSON_VALUE(WC.[value], ''$.value[0].text'') AS WidgetText
    FROM OPENJSON(@DataJson, ''$.widgets'') AS WC
) AS SourceData
PIVOT (
    MAX(WidgetText)
    FOR [key] IN (' + @PivotColumns + ')
) AS PivotTable'

EXEC sp_executesql @DynamicSQL, N'@DataJson NVARCHAR(MAX)', @DataJson

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