SQL Server：如何将逗号分隔列拆分为对应多行并保留核心列

测试数据准备 #

先创建示例数据表用于测试：

CREATE TABLE #Pets (
    id INT,
    name VARCHAR(50),
    Pet VARCHAR(100),
    Pet_Name VARCHAR(100)
);

INSERT INTO #Pets VALUES
(1, 'Adam', 'Dog, Dog, Cat', 'Cooper, Boot, Snowflake'),
(2, 'Ben', 'Dog, Hamster, Cat', 'Cliff, Hammy, Tiger');

解决方案1：使用OPENJSON（兼容SQL Server 2016+） #

利用OPENJSON将逗号分隔字符串转为JSON数组，通过数组索引（[key]）关联对应位置的Pet和Pet_Name：

SELECT 
    p.id,
    p.name,
    LTRIM(j1.value) AS Pet,
    LTRIM(j2.value) AS Pet_Name
FROM #Pets p
CROSS APPLY OPENJSON('["' + REPLACE(p.Pet, ', ', '","') + '"]') j1
CROSS APPLY OPENJSON('["' + REPLACE(p.Pet_Name, ', ', '","') + '"]') j2
WHERE j1.[key] = j2.[key];

• REPLACE将, 替换为","，把原始字符串拼接成合法的JSON数组格式
• OPENJSON解析数组后，[key]字段对应数组元素的索引位置，通过相等条件确保Pet和Pet_Name一一对应
• LTRIM用于去除每个拆分项前的空格

解决方案2：使用STRING_SPLIT（SQL Server 2022+） #

SQL Server 2022及以上版本的STRING_SPLIT支持enable_ordinal参数，直接返回拆分项的顺序号，简化关联逻辑：

SELECT 
    p.id,
    p.name,
    LTRIM(s1.value) AS Pet,
    LTRIM(s2.value) AS Pet_Name
FROM #Pets p
CROSS APPLY STRING_SPLIT(p.Pet, ', ', 1) s1
CROSS APPLY STRING_SPLIT(p.Pet_Name, ', ', 1) s2
WHERE s1.ordinal = s2.ordinal;

• 第三个参数1开启返回ordinal字段，代表拆分项在原字符串中的顺序
• 通过ordinal相等关联，确保Pet和Pet_Name的对应关系准确

这两种方法都无需创建临时表，直接通过CROSS APPLY完成拆分与关联，比临时表方案更简洁高效。

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