如何使用STRING_SPLIT拆分存在关联的逗号分隔字符串列？

嘿，这个场景我太熟了！因为STRING_SPLIT本身默认不会返回拆分项的顺序索引，直接拆两列的话很容易把对应关系搞混——毕竟SQL里的集合默认是无序的，哪怕看起来顺序对，也不保证每次都稳定对吧？下面分两种情况给你靠谱的解决方案：

情况1：使用SQL Server 2022及以上版本 #

SQL Server 2022给STRING_SPLIT加了第三个参数enable_ordinal，设为1就能直接返回每个拆分项的顺序序号（ordinal列），这时候关联两列就非常简单：

首先先建个示例表模拟你的数据：

CREATE TABLE #TempData (
    PrimaryKeyID INT,
    column1 VARCHAR(100),
    column2 VARCHAR(100)
);

INSERT INTO #TempData VALUES
(1, 'a,b,c', '1,2,3'),
(2, 'x,y', '4,5');

然后用带序号的STRING_SPLIT关联：

SELECT 
    td.PrimaryKeyID,
    s1.value AS colA_Value,
    s2.value AS colB_Value
FROM #TempData td
CROSS APPLY STRING_SPLIT(td.column1, ',', 1) s1
CROSS APPLY STRING_SPLIT(td.column2, ',', 1) s2
WHERE s1.ordinal = s2.ordinal;

这个方法直接利用官方提供的序号，既简洁又可靠，完全不用担心顺序问题。

情况2：使用SQL Server 2016-2019版本 #

旧版本的STRING_SPLIT没有ordinal参数，这时候用OPENJSON是更稳妥的选择——因为它会返回数组的索引（key列，从0开始），而且JSON数组的顺序是严格保证的：

SELECT 
    td.PrimaryKeyID,
    JSON_VALUE(j1.value, '$') AS colA_Value,
    JSON_VALUE(j2.value, '$') AS colB_Value
FROM #TempData td
CROSS APPLY OPENJSON('["' + REPLACE(td.column1, ',', '","') + '"]') j1
CROSS APPLY OPENJSON('["' + REPLACE(td.column2, ',', '","') + '"]') j2
WHERE j1.[key] = j2.[key];

原理是把逗号分隔的字符串转成标准的JSON数组，再用OPENJSON解析，通过相等的key值关联对应位置的项。这个方法在2016及以上版本都能用，比自己用ROW_NUMBER()拼序号要可靠得多（毕竟旧版本STRING_SPLIT的顺序官方不做保证）。

额外提醒 #

虽然当前工作必须用这种非规范化的格式，但如果有机会的话，还是建议把数据拆成规范化的子表（比如一个主表，一个关联子表存每个PrimaryKeyID对应的键值对），这样后续查询、维护和性能优化都会轻松很多。

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