单表两列字符串拆分：解决STRING_SPLIT产生的笛卡尔积问题

你遇到的核心问题很典型：STRING_SPLIT默认只返回拆分后的文本值，不保留拆分项在原字符串中的位置索引，所以两次独立的CROSS APPLY会把每个城市和每个销售额做笛卡尔积，自然产生大量重复记录。下面分两种场景给出解决方案：

方案1：SQL Server 2022及以上版本（推荐） #

从SQL Server 2022开始，STRING_SPLIT新增了enable_ordinal参数，可以返回每个拆分项的位置序号（从1开始）。我们可以通过这个序号来关联对应位置的城市和销售额：

SELECT 
    t.ID,
    splitC.Value AS City,
    splitS.Value AS Sales
FROM YourTable t
CROSS APPLY STRING_SPLIT(t.City, ',', 1) splitC
CROSS APPLY STRING_SPLIT(t.Sales, ',', 1) splitS
WHERE splitC.ordinal = splitS.ordinal;

说明： #

• enable_ordinal=1会让函数额外返回ordinal列，代表当前拆分项在原字符串中的位置
• 通过WHERE splitC.ordinal = splitS.ordinal，确保每个城市只匹配对应位置的销售额，完美避免笛卡尔积
• 空值（比如Paris对应的空销售额）会被正确保留

方案2：SQL Server 2022以下版本 #

如果你的版本不支持enable_ordinal，可以用XML拆分法来生成位置序号，再关联结果：

WITH SplitCities AS (
    SELECT 
        ID,
        City = x.value('.', 'VARCHAR(100)'),
        ordinal = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL))
    FROM YourTable t
    CROSS APPLY (SELECT CAST('<x>' + REPLACE(City, ',', '</x><x>') + '</x>' AS XML)) AS xmldata
    CROSS APPLY xmldata.nodes('x') AS n(x)
),
SplitSales AS (
    SELECT 
        ID,
        Sales = x.value('.', 'VARCHAR(100)'),
        ordinal = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL))
    FROM YourTable t
    CROSS APPLY (SELECT CAST('<x>' + REPLACE(Sales, ',', '</x><x>') + '</x>' AS XML)) AS xmldata
    CROSS APPLY xmldata.nodes('x') AS n(x)
)
SELECT 
    sc.ID,
    sc.City,
    ss.Sales
FROM SplitCities sc
JOIN SplitSales ss ON sc.ID = ss.ID AND sc.ordinal = ss.ordinal;

说明： #

• 先把逗号分隔的字符串转换成XML节点，再拆分每个节点得到单独的城市/销售额
• 用ROW_NUMBER()按ID分组生成序号，保证序号和原字符串的拆分顺序一致
• 最后通过ID+序号关联两个CTE，得到一一对应的记录

注意：把代码中的YourTable替换成你实际使用的表名即可。

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