TSQL解析制表符分隔列并合并关联记录问题

将错误日志平面文件导入SQL Server时，需把制表符分隔的[Column 0]列解析为多列。期望实现两个目标：

• 捕获STRING_SPLIT()拆分[Column 0]后的最大列数，用于创建动态PIVOT（暂可搁置）；
• 将解析后的列与原记录的其余字段正确合并。

当前第二个目标存在问题：现有查询会将原表每条记录拆分为多行，需合并为单条记录。

示例表定义 #

DECLARE @SAMPLE_TABLE table(
    [Column 0] nvarchar(4000),
    [Filename] nvarchar(260),
    FileExtention varchar(255),
    DateTimeStamp datetime,
    CustomerNumber varchar(255),
    FileType varchar(255),
    ImportSetNumber varchar(255)
)

示例数据 #

现有查询代码 #

;WITH
CTE_Columns AS(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) 'MyRowID',
            [Filename],
            FileExtention,
            DateTimeStamp,
            CustomerNumber,
            FileType,
            ImportSetNumber,
            A.ColID 'ColumnNumber',
            A.Cols 'ColumnValue'
    FROM @SAMPLE_TABLE
    CROSS APPLY (
        SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ColID,
                value [Cols]
                FROM STRING_SPLIT([Column 0], CHAR(9))  -- split by tab character
    )A
)

SELECT MyRowID,
        [Filename],
        FileExtention,
        DateTimeStamp,
        CustomerNumber,
        FileType,
        ImportSetNumber,
        NULLIF(TRIM([1]), '') 'FirstColumn',
        NULLIF(TRIM([2]), '') 'SecondColumn',
        NULLIF(TRIM([3]), '') 'ThirdColumn',
        NULLIF(TRIM([4]), '') 'FourthColumn'
FROM (
        SELECT MyRowID,
                [Filename],
                FileExtention,
                DateTimeStamp,
                CustomerNumber,
                FileType,
                ImportSetNumber,
                ColumnNumber,
                ColumnValue
        FROM CTE_Columns
)Q
PIVOT(MAX(Q.ColumnValue) FOR ColumnNumber IN([1], [2], [3], [4])) PIV
ORDER BY CustomerNumber,
            ImportSetNumber

问题点 #

现有查询中，MyRowID是全局唯一的行号，导致原表每条记录拆分后的行无法聚合。最终结果会把原单条记录拆分为多行，而期望将拆分后的列合并回原记录，得到如下目标结果：

目标结果 #

解决方案 #

问题核心是MyRowID的生成逻辑错误，应该为原表的每条记录分配唯一ID，而不是全局行号。修改CTE中的ID生成逻辑，确保同一原记录拆分后的行拥有相同的ID，这样PIVOT时就能正确聚合。

修改后的查询代码 #

;WITH
CTE_OriginalRows AS(
    -- 为原表每条记录分配唯一ID
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS OriginalRowID,
           [Column 0],
           [Filename],
           FileExtention,
           DateTimeStamp,
           CustomerNumber,
           FileType,
           ImportSetNumber
    FROM @SAMPLE_TABLE
),
CTE_Columns AS(
    SELECT OriginalRowID,
            [Filename],
            FileExtention,
            DateTimeStamp,
            CustomerNumber,
            FileType,
            ImportSetNumber,
            A.ColID 'ColumnNumber',
            A.Cols 'ColumnValue'
    FROM CTE_OriginalRows
    CROSS APPLY (
        SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ColID,
                value [Cols]
                FROM STRING_SPLIT([Column 0], CHAR(9))  -- split by tab character
    )A
)

SELECT [Filename],
        FileExtention,
        DateTimeStamp,
        CustomerNumber,
        FileType,
        ImportSetNumber,
        NULLIF(TRIM([1]), '') 'FirstColumn',
        NULLIF(TRIM([2]), '') 'SecondColumn',
        NULLIF(TRIM([3]), '') 'ThirdColumn',
        NULLIF(TRIM([4]), '') 'FourthColumn'
FROM (
        SELECT OriginalRowID,
                [Filename],
                FileExtention,
                DateTimeStamp,
                CustomerNumber,
                FileType,
                ImportSetNumber,
                ColumnNumber,
                ColumnValue
        FROM CTE_Columns
)Q
PIVOT(MAX(Q.ColumnValue) FOR ColumnNumber IN([1], [2], [3], [4])) PIV
ORDER BY CustomerNumber,
            ImportSetNumber

关键说明 #

1. 新增CTE_OriginalRows，为原表每条记录生成唯一的OriginalRowID，确保同一原记录拆分后的所有行共享此ID；
2. 去掉原查询中的全局MyRowID，改用OriginalRowID作为聚合依据；
3. PIVOT时，MAX(Q.ColumnValue)会按OriginalRowID分组，将同一原记录的拆分列聚合到一行；
4. 最终结果中，原表每条记录对应一行，拆分后的列正确合并。

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