SQL Server中将多列逗号分隔值拆分为行的问题求助

你现在遇到的核心问题是：两次独立的CROSS APPLY把两列拆分后的结果做了笛卡尔积，没有按照元素的原始位置来关联ListType_ID和Values的拆分结果。要得到目标输出，我们需要在拆分时记录每个值的位置，再按位置匹配对应行。

下面给你两种可行的解决方案：

方法一：使用STRING_SPLIT（SQL Server 2016及以上版本） #

从SQL Server 2016开始，STRING_SPLIT支持ordinal参数（需数据库兼容级别设为130+），这个参数会返回拆分后每个元素的原始位置，我们可以直接用它来关联两列的对应值：

DECLARE @TEMP AS TABLE ( [Rule_ID] INT, [ListType_ID] VARCHAR(MAX), [Values] VARCHAR(MAX) )
INSERT INTO @TEMP
SELECT 1, '1,2', '100,200' UNION ALL
SELECT 2, '3,4', '300,400'

SELECT 
    t.Rule_ID,
    s1.value AS ListType_ID,
    s2.value AS [Values]
FROM @TEMP t
CROSS APPLY STRING_SPLIT(t.ListType_ID, ',', 1) s1
CROSS APPLY STRING_SPLIT(t.[Values], ',', 1) s2
WHERE s1.ordinal = s2.ordinal
ORDER BY t.Rule_ID, s1.ordinal;

这个查询会严格按照拆分后的位置（ordinal）匹配两列的元素，直接得到你想要的结果。

方法二：XML拆分+位置序号（兼容低版本SQL Server） #

如果你的SQL Server版本低于2016，可以用XML拆分的方式，同时通过ROW_NUMBER()标记每个拆分元素的位置，再关联位置相同的行：

DECLARE @TEMP AS TABLE ( [Rule_ID] INT, [ListType_ID] VARCHAR(MAX), [Values] VARCHAR(MAX) )
INSERT INTO @TEMP
SELECT 1, '1,2', '100,200' UNION ALL
SELECT 2, '3,4', '300,400'

WITH SplitListType AS (
    SELECT 
        Rule_ID,
        CAST('<M>' + REPLACE(ListType_ID, ',', '</M><M>') + '</M>' AS XML) AS ListType_XML
    FROM @TEMP
),
ListTypeWithOrdinal AS (
    SELECT 
        Rule_ID,
        m.value('.', 'VARCHAR(100)') AS ListType_ID,
        ROW_NUMBER() OVER (PARTITION BY Rule_ID ORDER BY (SELECT NULL)) AS ordinal
    FROM SplitListType
    CROSS APPLY ListType_XML.nodes('/M') AS t(m)
),
SplitValues AS (
    SELECT 
        Rule_ID,
        CAST('<M>' + REPLACE([Values], ',', '</M><M>') + '</M>' AS XML) AS Values_XML
    FROM @TEMP
),
ValuesWithOrdinal AS (
    SELECT 
        Rule_ID,
        m.value('.', 'VARCHAR(100)') AS [Values],
        ROW_NUMBER() OVER (PARTITION BY Rule_ID ORDER BY (SELECT NULL)) AS ordinal
    FROM SplitValues
    CROSS APPLY Values_XML.nodes('/M') AS t(m)
)
SELECT 
    l.Rule_ID,
    l.ListType_ID,
    v.[Values]
FROM ListTypeWithOrdinal l
JOIN ValuesWithOrdinal v ON l.Rule_ID = v.Rule_ID AND l.ordinal = v.ordinal
ORDER BY l.Rule_ID, l.ordinal;

这里我们先分别拆分两列并给每个元素添加位置序号，再通过Rule_ID和序号关联，保证第n个ListType_ID对应第n个Values。

为什么你的原查询会出错？ #

你的原查询中，两次CROSS APPLY是完全独立的操作：对于每个Rule_ID，拆分后的ListType_ID的每一行都会和拆分后的Values的每一行进行匹配，所以得到了笛卡尔积（每个Rule_ID生成2*2=4行）。只有通过位置序号关联，才能实现同位置元素的一一对应。

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