SQL Server多列字符串拆分并转对应多行多列的实现

原始数据表 #

目标数据表 #

原CTE方案的问题分析 #

你提供的CTE存在几个关键错误，会导致结果不符合预期：

• STRING_SPLIT的第三个参数是enable_ordinal，需传入bit类型值1而非空字符串''，只有设为1才会返回拆分值的原始位置序号，这是保证同位置匹配的核心
• 第三个CTE z错误使用了extenddatehistory列，实际应该拆分newduedatehistory
• 最终查询中的x.loanid是笔误，应为x.Id
• 用row_number() over (partition by id order by x.value)生成序号是错误的：若拆分后的日期不是递增顺序，序号会和原始字符串中的位置完全脱节

优化后的正确方案 #

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

利用STRING_SPLIT新增的ordinal参数直接获取拆分值的原始位置，精准匹配各列同位置数据：

WITH SplitOld AS (
    SELECT 
        Id,
        value AS oldduedatehistory,
        ordinal AS pos
    FROM YourTable -- 替换为你的实际表名
    CROSS APPLY STRING_SPLIT(oldduedatehistory, ',', 1)
),
SplitExtend AS (
    SELECT 
        Id,
        value AS extenddatehistory,
        ordinal AS pos
    FROM YourTable
    CROSS APPLY STRING_SPLIT(extenddatehistory, ',', 1)
),
SplitNew AS (
    SELECT 
        Id,
        value AS newduedatehistory,
        ordinal AS pos
    FROM YourTable
    CROSS APPLY STRING_SPLIT(newduedatehistory, ',', 1)
)
SELECT 
    s.Id,
    s.oldduedatehistory,
    e.extenddatehistory,
    n.newduedatehistory
FROM SplitOld s
JOIN SplitExtend e ON s.Id = e.Id AND s.pos = e.pos
JOIN SplitNew n ON s.Id = n.Id AND s.pos = n.pos
ORDER BY s.Id, s.pos;

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

由于旧版本STRING_SPLIT不支持ordinal参数，需自定义带序号的拆分函数：

-- 创建自定义拆分函数
CREATE FUNCTION dbo.SplitStringWithOrdinal
(
    @String NVARCHAR(MAX),
    @Delimiter NVARCHAR(5)
)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        value,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ordinal
    FROM STRING_SPLIT(@String, @Delimiter)
);

然后使用该函数实现拆分匹配：

WITH SplitOld AS (
    SELECT 
        Id,
        value AS oldduedatehistory,
        ordinal AS pos
    FROM YourTable
    CROSS APPLY dbo.SplitStringWithOrdinal(oldduedatehistory, ',')
),
SplitExtend AS (
    SELECT 
        Id,
        value AS extenddatehistory,
        ordinal AS pos
    FROM YourTable
    CROSS APPLY dbo.SplitStringWithOrdinal(extenddatehistory, ',')
),
SplitNew AS (
    SELECT 
        Id,
        value AS newduedatehistory,
        ordinal AS pos
    FROM YourTable
    CROSS APPLY dbo.SplitStringWithOrdinal(newduedatehistory, ',')
)
SELECT 
    s.Id,
    s.oldduedatehistory,
    e.extenddatehistory,
    n.newduedatehistory
FROM SplitOld s
JOIN SplitExtend e ON s.Id = e.Id AND s.pos = e.pos
JOIN SplitNew n ON s.Id = n.Id AND s.pos = n.pos
ORDER BY s.Id, s.pos;

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