如何将SQL表动态转换为指定格式的JSON？

要实现将SQL表转换为指定的键值对JSON格式，同时适配表的列新增变化，可以通过动态SQL拼接每个列的处理逻辑，最终生成符合要求的JSON输出。

核心思路 #

将表中每一列的列名作为property值，列的具体内容作为value值，通过UNION ALL将所有列的结果合并为行记录，最后用FOR JSON PATH生成目标结构。

适配SQL Server 2017+的脚本 #

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

-- 动态拼接每个列的查询语句
SELECT @cols = STRING_AGG(
    CONCAT(N'SELECT ''', name, ''' AS property, CAST(', QUOTENAME(name), ' AS NVARCHAR(MAX)) AS value FROM ##T'),
    N' UNION ALL '
)
FROM sys.columns
WHERE object_id = OBJECT_ID(N'##T');

-- 生成并执行最终SQL
SET @sql = N'SELECT * FROM (' + @cols + N') AS t FOR JSON PATH';
EXEC sp_executesql @sql;

适配SQL Server 2016及以下版本的脚本 #

（此版本不支持STRING_AGG，改用FOR XML PATH拼接）

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

-- 动态拼接每个列的查询语句
SELECT @cols = STUFF(
    (SELECT N' UNION ALL SELECT ''' + name + ''' AS property, CAST(' + QUOTENAME(name) + ' AS NVARCHAR(MAX)) AS value FROM ##T'
     FROM sys.columns
     WHERE object_id = OBJECT_ID(N'##T')
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
    1, 11, N''
);

-- 生成并执行最终SQL
SET @sql = N'SELECT * FROM (' + @cols + N') AS t FOR JSON PATH';
EXEC sp_executesql @sql;

关键说明 #

• 动态列适配：通过sys.columns获取表的所有列名，自动适配新增列，无需手动修改脚本。
• 数据类型统一：用CAST(列名 AS NVARCHAR(MAX))将所有列值转为字符串类型，避免不同数据类型在JSON中出现格式差异。
• 列名安全处理：使用QUOTENAME包裹列名，避免列名含特殊字符时出现SQL语法错误或注入风险。

内容的提问来源于stack exchange，提问作者Diyarbek Maksudov