SQL Server中如何对多列应用字符串拆分函数实现值拆分为多行

核心逻辑说明：拆分后必须以拆分结果的序列位置作为关联匹配条件，否则会出现name和value错位不对应的问题

适配SQL Server 2022及以上版本（使用内置STRING_SPLIT函数实现） #

SQL Server 2022版本的STRING_SPLIT函数支持开启拆分序号返回的参数，可直接基于序号做精准匹配，代码实现如下：

-- 引入测试源数据
WITH source_data AS (
    SELECT 1 AS id, 'a, b' AS name, '12, 34' AS value
)
SELECT
    sd.id,
    -- 清除拆分后字符串前后的冗余空格
    LTRIM(RTRIM(n.[value])) AS name,
    LTRIM(RTRIM(v.[value])) AS value
FROM source_data sd
-- 拆分name列，开启序号返回
CROSS APPLY STRING_SPLIT(sd.name, ',', 1) n
-- 拆分value列，开启序号返回
CROSS APPLY STRING_SPLIT(sd.value, ',', 1) v
-- 关键关联条件：两个拆分列的序号完全相等，保证一一匹配
WHERE n.ordinal = v.ordinal;

执行以上代码即可得到和目标输出完全一致的结果结构。

适配SQL Server 2008及以上低版本（无内置序号拆分函数的场景） #

如果使用的SQL Server版本不支持STRING_SPLIT的序号返回功能，可通过XML拆分的方式生成带序号的拆分结果，无需自定义函数，兼容大部分旧版本环境：

WITH source_data AS (
    SELECT 1 AS id, 'a, b' AS name, '12, 34' AS value
),
-- 生成name列带序号的拆分结果
name_split_result AS (
    SELECT
        sd.id,
        LTRIM(RTRIM(s.x.value('.', 'VARCHAR(100)'))) AS split_name,
        ROW_NUMBER() OVER(PARTITION BY sd.id ORDER BY (SELECT 0)) AS row_seq
    FROM source_data sd
    CROSS APPLY (
        -- 将逗号分隔的字符串转为XML节点结构
        SELECT CAST('<x>' + REPLACE(sd.name, ',', '</x><x>') + '</x>' AS XML) AS xml_node
    ) t
    CROSS APPLY xml_node.nodes('/x') s(x)
),
-- 生成value列带序号的拆分结果
value_split_result AS (
    SELECT
        sd.id,
        LTRIM(RTRIM(s.x.value('.', 'VARCHAR(100)'))) AS split_value,
        ROW_NUMBER() OVER(PARTITION BY sd.id ORDER BY (SELECT 0)) AS row_seq
    FROM source_data sd
    CROSS APPLY (
        SELECT CAST('<x>' + REPLACE(sd.value, ',', '</x><x>') + '</x>' AS XML) AS xml_node
    ) t
    CROSS APPLY xml_node.nodes('/x') s(x)
)
-- 按相同序号关联得到对应结果
SELECT
    nsr.id,
    nsr.split_name AS name,
    vsr.split_value AS value
FROM name_split_result nsr
INNER JOIN value_split_result vsr
    ON nsr.id = vsr.id
    AND nsr.row_seq = vsr.row_seq;

实操注意事项 #

• 上线前需校验同一id下两列拆分后的元素数量是否一致，若数量不匹配，关联后会出现数据丢失，需提前对异常格式的源数据做清洗。
• 如果源字符串本身包含拆分用的逗号，需更换适配场景的分隔符，避免拆分错位。

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