Flat File导入SQL Server：MT940多段数据结构化查询方案

需求说明 #

读取TXT/Flat文件中的MT940格式数据，将冒号前的标识作为列名，冒号后的内容作为对应列的记录值，把多段数据转换为每行对应一段数据的结构化表格。

示例MT940数据 #

{1:F01SBZAZAJJXXXX9999999999}{2:I940SBICMWMXXXXXN}{4:
:20:D424A100110011E4
:25:020083203
:28C:49/1
:60F:C140106ZAR1029873,62
:61:1401060106DR5000,NTRF99999999//NONREF20140106-13175-016050001844421
:86:/PREF/ZA000520CATS THIRD PARTY PAYMENT
:62F:C140106ZAR0,00
-}

{1:F01SBZAZAJJXXXX9999999999}{2:I940SBICMWMXXXXXN}{4:
:20:D3DE7040110011E4
:25:020083204
:28C:51/1
:60F:C140106NAD1030073,
:61:1401060106DR5000,NTRF20140106-13175-0//NONREF20140106-13175-016050001844421
:86:/PREF/NA000520TRANSFER
:62F:C140106NAD0,00
-}

现有问题 #

当前的SQL PIVOT查询仅能处理单段数据，无法实现全量多段数据的转换，无法得到每行对应一段MT940数据的结构化结果。

现有查询代码 #

SELECT [20], [25], [28C], [60F], [61], [86], [62F] 
FROM
(SELECT column2, column3 FROM [dbo].[Sample MT940]) AS Source_Table
PIVOT
(MAX(column3)
FOR 
column2 in ([20], [25], [28C], [60F], [61], [86], [62F])
) AS PIVOT_TABLE

解决方案 #

问题核心是缺少分段标识，现有查询没有区分不同的MT940数据段，导致PIVOT时会把所有同标识的字段值合并。需要先给每个数据段添加唯一的分组ID，具体实现如下：

WITH SegmentedData AS (
    SELECT 
        column2, 
        column3,
        -- 统计当前行之前出现的段起始标记数量，作为分段ID
        SUM(CASE WHEN column1 LIKE '%{1:F%' THEN 1 ELSE 0 END) OVER (ORDER BY (SELECT NULL)) AS SegmentID
    FROM [dbo].[Sample MT940]
    -- 过滤掉非字段行（如段头、段尾）
    WHERE column2 IS NOT NULL AND column2 LIKE ':[0-9A-Z]%'
)
SELECT [20], [25], [28C], [60F], [61], [86], [62F]
FROM SegmentedData
PIVOT (
    MAX(column3)
    FOR column2 IN ([20], [25], [28C], [60F], [61], [86], [62F])
) AS PivotResult;

关键说明 #

• SUM(CASE...) OVER(ORDER BY...)：为每一段数据分配相同的SegmentID，确保同一段的字段被归为一组。
• WHERE子句：过滤掉MT940的段头（{1:F...}等）和段尾（-}）行，只保留带冒号的字段行。
• 如果是从TXT文件导入数据，也可以在导入阶段（如SSIS、自定义脚本）直接为每一段数据添加SegmentID，每遇到一个新的{1:F标记就递增ID。

预期结果 #

生成的结构化表格每行对应一段MT940数据，示例结果如下：

内容的提问来源于stack exchange，提问作者Jimrosy P Madzokere