如何用SQL Server 2022的STRING_SPLIT拆分键值对为表格？

你需要通过两次基于集合的拆分操作，搭配字符串清理和去重逻辑实现需求，具体方案如下：

完整实现代码 #

-- 创建示例临时表
create table #mashed (mashedtext nvarchar(1600))

insert into #mashed 
values ('"id":"one", "code":"aaa", "dev":"yes"')
insert into #mashed 
values ('"id":"two", "code":"bbb", "dev":"yes"')
insert into #mashed 
values ('"id":"three", "code":"ccc", "dev":"no"')
insert into #mashed 
values ('"id":"three", "code":"bbb", "help":"no" , "rid":"6"')

-- 核心拆分逻辑
WITH KeyValuePairs AS (
    -- 第一步：拆分每行字符串为单个键值对，同时清理前后的引号和空格
    SELECT 
        TRIM(' "' FROM kvp.value) AS CleanKeyValuePair
    FROM #mashed
    CROSS APPLY STRING_SPLIT(mashedtext, ',') AS kvp
), SplitPairs AS (
    -- 第二步：拆分每个键值对为key和value，用行号区分两部分
    SELECT
        MAX(CASE WHEN rn = 1 THEN TRIM(part.value) END) AS [key],
        MAX(CASE WHEN rn = 2 THEN TRIM(part.value) END) AS [value]
    FROM KeyValuePairs
    CROSS APPLY (
        SELECT 
            value,
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
        FROM STRING_SPLIT(CleanKeyValuePair, ':')
    ) AS part
    GROUP BY CleanKeyValuePair
)
-- 去重后输出最终结果
SELECT DISTINCT [key], [value]
FROM SplitPairs
ORDER BY [key], [value];

-- 清理临时表
DROP TABLE #mashed;

逻辑说明 #

1. 第一次拆分（KeyValuePairs CTE）：

   • 用CROSS APPLY关联原表与STRING_SPLIT，将每行字符串按逗号拆分为独立键值对，这是纯集合式操作，避免逐行处理（RBAR）。
   • 通过TRIM(' "' FROM ...)移除键值对前后的引号和空格，比如把 "dev":"yes"转换为dev:yes。

2. 第二次拆分（SplitPairs CTE）：

   • 再次调用STRING_SPLIT将清理后的键值对按冒号拆分，用ROW_NUMBER()给拆分出的两部分编号（1对应key，2对应value）。
   • 借助条件聚合（MAX(CASE...)）将同一键值对的key和value正确配对。

3. 最终去重：

   • 用DISTINCT剔除重复的键值对，按key和value排序后得到目标结果。

你之前的写法错误在于直接在STRING_SPLIT中嵌套查询，正确姿势是用CROSS APPLY关联原表与拆分结果，实现批量的集合式处理。

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