SQL Server（2017之前版本）将列唯一值转为JSON键值对

原表数据 #

目标JSON格式 #

{"Column A": ["Xxx", "Rrr"], "Column B": ["Yyyy", "zzzz"]}

由于SQL Server 2017之前没有STRING_AGG和原生JSON数组构造函数，我们可以通过FOR XML PATH拼接去重值，手动构造符合要求的JSON结构：

完整SQL代码 #

DECLARE @ColumnA NVARCHAR(MAX), @ColumnB NVARCHAR(MAX)
-- 替换为你的实际表名
DECLARE @TableName NVARCHAR(128) = 'YourTableName'

-- 生成Column A的去重值数组字符串
SELECT @ColumnA = '["' + STUFF(
    (SELECT DISTINCT '","' + REPLACE([Column A], '"', '\"')
     FROM YourTableName
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
    1, 2, ''
) + '"]'

-- 生成Column B的去重值数组字符串
SELECT @ColumnB = '["' + STUFF(
    (SELECT DISTINCT '","' + REPLACE([Column B], '"', '\"')
     FROM YourTableName
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
    1, 2, ''
) + '"]'

-- 拼接成最终JSON结果
SELECT '{"Column A": ' + @ColumnA + ', "Column B": ' + @ColumnB + '}' AS ResultJSON

代码说明 #

1. 去重与拼接：用SELECT DISTINCT提取列的唯一值，通过FOR XML PATH('')将多行结果拼接为带分隔符的字符串，TYPE参数确保特殊字符（如<、>）被正确转义。
2. 清理开头分隔符：STUFF函数移除拼接后字符串开头多余的","，保证数组格式正确。
3. 转义特殊字符：REPLACE([列名], '"', '\"')处理值中包含的双引号，避免破坏JSON结构的合法性。
4. 构造JSON对象：手动将拼接好的字符串包裹成数组格式，再组合成完整的JSON键值对结构。

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