SQL Server 2012：如何对同表两分隔列执行CROSS JOIN？

嘿，这个需求我之前帮不少人处理过，SQL Server 2012因为还没有内置的STRING_SPLIT函数（那是2016及以后版本才有的），所以得用点替代方案。下面给你两种可行的方法，都能实现你要的：把每行里Delimited_Col_1和Delimited_Col_2的CSV值做行内笛卡尔积，最后合并所有行的结果。

方法一：用XML拆分（无需创建函数） #

这种方法不用额外创建函数，适合临时查询使用。假设你的表叫YourTable，结构包含唯一标识列（比如ID）和两个CSV列，先给你一个带测试数据的完整示例：

测试数据准备 #

-- 创建测试表
CREATE TABLE YourTable (
    ID INT PRIMARY KEY,
    Delimited_Col_1 VARCHAR(MAX),
    Delimited_Col_2 VARCHAR(MAX)
);

-- 插入测试数据
INSERT INTO YourTable VALUES
(1, 'A,B,C', 'X,Y'),
(2, 'D,E', 'Z'),
(3, 'F', 'G,H,I');

实现查询 #

WITH SplitCol1 AS (
    SELECT 
        ID,
        -- 去掉拆分后值的前后空格
        LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) AS Val1
    FROM (
        -- 把CSV字符串转成XML格式，用逗号分隔节点
        SELECT 
            ID,
            CAST('<Item>' + REPLACE(Delimited_Col_1, ',', '</Item><Item>') + '</Item>' AS XML) AS XmlData
        FROM YourTable
        -- 过滤空值和空字符串，避免无效拆分
        WHERE Delimited_Col_1 IS NOT NULL AND Delimited_Col_1 != ''
    ) AS RawData
    -- 拆分XML节点为行
    CROSS APPLY XmlData.nodes('/Item') AS Split(a)
),
SplitCol2 AS (
    SELECT 
        ID,
        LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) AS Val2
    FROM (
        SELECT 
            ID,
            CAST('<Item>' + REPLACE(Delimited_Col_2, ',', '</Item><Item>') + '</Item>' AS XML) AS XmlData
        FROM YourTable
        WHERE Delimited_Col_2 IS NOT NULL AND Delimited_Col_2 != ''
    ) AS RawData
    CROSS APPLY XmlData.nodes('/Item') AS Split(a)
)
-- 关联同一行的拆分结果，得到笛卡尔积
SELECT sc1.Val1, sc2.Val2
FROM SplitCol1 sc1
INNER JOIN SplitCol2 sc2 ON sc1.ID = sc2.ID
ORDER BY sc1.ID, sc1.Val1, sc2.Val2;

结果说明 #

执行后会得到每个CSV列值的组合，比如第一行的A/B/C和X/Y会生成A-X、A-Y、B-X、B-Y、C-X、C-Y，所有行的结果会合并在一起。

方法二：自定义表值拆分函数（复用性更强） #

如果需要多次执行这类查询，创建一个通用的拆分函数会更方便。

创建拆分函数 #

CREATE FUNCTION dbo.SplitCSV (@InputString VARCHAR(MAX), @Delimiter CHAR(1))
RETURNS @OutputTable TABLE (Value VARCHAR(100))
AS
BEGIN
    DECLARE @StartIndex INT = 1;
    DECLARE @EndIndex INT;

    -- 确保字符串末尾有分隔符，避免漏拆最后一个值
    IF RIGHT(@InputString, 1) != @Delimiter
    BEGIN
        SET @InputString = @InputString + @Delimiter;
    END;

    -- 循环拆分字符串
    WHILE CHARINDEX(@Delimiter, @InputString) > 0
    BEGIN
        SET @EndIndex = CHARINDEX(@Delimiter, @InputString);
        -- 插入拆分后的值（去掉前后空格）
        INSERT INTO @OutputTable(Value)
        SELECT LTRIM(RTRIM(SUBSTRING(@InputString, @StartIndex, @EndIndex - @StartIndex)));
        -- 截取剩余字符串继续拆分
        SET @InputString = SUBSTRING(@InputString, @EndIndex + 1, LEN(@InputString));
    END;

    RETURN;
END;

使用函数实现查询 #

SELECT 
    sc1.Value AS Val1,
    sc2.Value AS Val2
FROM YourTable t
-- 拆分Delimited_Col_1
CROSS APPLY dbo.SplitCSV(t.Delimited_Col_1, ',') sc1
-- 拆分Delimited_Col_2，和上面的拆分结果做行内笛卡尔积
CROSS APPLY dbo.SplitCSV(t.Delimited_Col_2, ',') sc2
-- 过滤空值
WHERE t.Delimited_Col_1 IS NOT NULL AND t.Delimited_Col_1 != ''
  AND t.Delimited_Col_2 IS NOT NULL AND t.Delimited_Col_2 != ''
ORDER BY t.ID, sc1.Value, sc2.Value;

优势说明 #

这个写法更简洁，CROSS APPLY会自动把每行的CSV列拆分成多行，两个CROSS APPLY组合就直接实现了行内的笛卡尔积，后续再用类似需求直接调用函数即可。

注意事项 #

• 如果你的CSV值里包含带逗号的内容（比如"John,Doe"），上述两种方法都会失效，这种情况需要用更复杂的拆分逻辑（比如处理引号），不过如果是普通的无嵌套逗号的CSV，这两种方法完全够用。
• 3000行数据量不大，两种方法的性能都能满足需求，自定义函数可能比XML方法稍微慢一点，但差异可以忽略。

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