SQL中如何关联STRING_SPLIT拆分后ean_upc与is_approved的对应项？

适用场景 #

现有表包含product_id、ean_upc（分号分隔多个EAN码）、is_approved（分号分隔对应位置的审批标志），需要将拆分后的每个EAN码与同位置的审批标志关联，生成包含product_id、单个EAN码、对应审批标志的结果集。

SQL Server 2022及以上版本解法 #

利用STRING_SPLIT新增的enable_ordinal参数（开启后返回元素序号），直接通过序号关联对应位置的元素：

SELECT 
    t.product_id,
    e.value AS ean_code,
    a.value AS is_approved
FROM 
    your_table t
CROSS APPLY 
    STRING_SPLIT(t.ean_upc, ';', 1) e
CROSS APPLY 
    STRING_SPLIT(t.is_approved, ';', 1) a
WHERE 
    e.ordinal = a.ordinal;

说明：enable_ordinal=1会让拆分结果包含ordinal列（从1开始的序号），通过e.ordinal = a.ordinal确保EAN码和审批标志位置一一对应。

SQL Server 2016-2019版本解法 #

此版本STRING_SPLIT无序号参数，可借助OPENJSON解析JSON数组获取索引：

SELECT 
    t.product_id,
    JSON_VALUE(e.value, '$') AS ean_code,
    JSON_VALUE(a.value, '$') AS is_approved
FROM 
    your_table t
CROSS APPLY 
    OPENJSON('["' + REPLACE(t.ean_upc, ';', '","') + '"]') e
CROSS APPLY 
    OPENJSON('["' + REPLACE(t.is_approved, ';', '","') + '"]') a
WHERE 
    e.[key] = a.[key];

说明：将分号分隔的字符串转换为JSON数组格式，OPENJSON返回的[key]字段为元素索引（从0开始），通过索引相等实现位置关联。

也可以使用XML拆分+行号生成的方式：

WITH SplitEAN AS (
    SELECT 
        product_id,
        ean_code = LTRIM(RTRIM(x.value('.', 'NVARCHAR(MAX)'))),
        ordinal = ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY (SELECT NULL))
    FROM 
        your_table t
    CROSS APPLY 
        (SELECT CAST('<x>' + REPLACE(t.ean_upc, ';', '</x><x>') + '</x>' AS XML)) AS xm
    CROSS APPLY 
        xm.nodes('/x') AS n(x)
),
SplitApproved AS (
    SELECT 
        product_id,
        is_approved = LTRIM(RTRIM(x.value('.', 'NVARCHAR(MAX)'))),
        ordinal = ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY (SELECT NULL))
    FROM 
        your_table t
    CROSS APPLY 
        (SELECT CAST('<x>' + REPLACE(t.is_approved, ';', '</x><x>') + '</x>' AS XML)) AS xm
    CROSS APPLY 
        xm.nodes('/x') AS n(x)
)
SELECT 
    se.product_id,
    se.ean_code,
    sa.is_approved
FROM 
    SplitEAN se
JOIN 
    SplitApproved sa ON se.product_id = sa.product_id AND se.ordinal = sa.ordinal;

说明：先将字符串转换为XML节点拆分，再通过ROW_NUMBER()按product_id分组生成序号，最后通过序号关联两个拆分结果。

注意事项 #

• 需保证ean_upc和is_approved两个字段的分号分隔元素数量一致，否则会出现部分元素无法匹配的情况；
• 根据实际数据情况，可调整LTRIM(RTRIM())是否保留，用于处理元素前后的空格；
• 以上为SQL Server专属解法，其他数据库（如MySQL、PostgreSQL）需使用对应数据库的拆分函数和序号生成方法。

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