如何拆分两列多值并对应显示还款金额与参考日期？

你的问题出在使用两次CROSS APPLY STRING_SPLIT时，会生成拆分后金额和参考值的笛卡尔积，导致数据重复。要实现金额和对应日期的一一匹配，需要让拆分后的每个元素带上对应位置的序号，再按序号关联。

方案1：SQL Server 2022及以上版本（支持STRING_SPLIT的ordinal参数） #

SQL Server 2022开始，STRING_SPLIT新增了ordinal参数，能返回拆分元素的原始位置索引。利用这个参数可以精准关联两列的对应元素：

SELECT 
    bil.REPAY_AMOUNT,
    RA.Value AS SPLIT_REPAY_AMOUNT,
    -- 从参考值中提取日期（假设日期是末尾固定格式，比如'YYYYMMDD'，可根据实际格式调整）
    RIGHT(RR.Value, 8) AS REPAY_DATE,
    bil.REPAY_ref
FROM InsightImport.dbo.AA_BILL_DETAILS bil
CROSS APPLY STRING_SPLIT(bil.REPAY_AMOUNT, N'', 1) RA
CROSS APPLY STRING_SPLIT(bil.REPAY_ref, N'', 1) RR
WHERE RA.ordinal = RR.ordinal
-- 过滤拆分后产生的空元素
AND RA.Value <> ''
AND RR.Value <> ''

方案2：SQL Server 2022以下版本（无ordinal参数） #

如果你的SQL Server版本不支持ordinal参数，需要自定义一个带序号的字符串拆分函数，再通过序号关联两列数据：

第一步：创建自定义拆分函数 #

CREATE FUNCTION dbo.SplitStringWithOrdinal
(
    @InputString NVARCHAR(MAX),
    @Delimiter NVARCHAR(10)
)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        Value,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ordinal
    FROM STRING_SPLIT(@InputString, @Delimiter)
    WHERE Value <> ''
)

第二步：使用函数执行关联查询 #

SELECT 
    bil.REPAY_AMOUNT,
    RA.Value AS SPLIT_REPAY_AMOUNT,
    RIGHT(RR.Value, 8) AS REPAY_DATE,
    bil.REPAY_ref
FROM InsightImport.dbo.AA_BILL_DETAILS bil
CROSS APPLY dbo.SplitStringWithOrdinal(bil.REPAY_AMOUNT, N'') RA
CROSS APPLY dbo.SplitStringWithOrdinal(bil.REPAY_ref, N'') RR
WHERE RA.ordinal = RR.ordinal

注意事项 #

• 日期提取逻辑：如果参考值中的日期格式不是末尾8位，可根据实际格式调整提取方式。比如日期是YYYY-MM-DD格式，可改用正则表达式（SQL Server 2017+支持）提取：

  REGEXP_REPLACE(RR.Value, N'.*(\d{4}-\d{2}-\d{2})', '$1') AS REPAY_DATE
  

• 数据一致性：需确保每行的Repay_Aount和Repay_Ref拆分后的元素数量一致，否则会导致部分数据无法匹配。若存在数量不匹配的情况，可根据需求添加额外的处理逻辑。

内容的提问来源于stack exchange，提问作者K H A N