如何将#SourceData按TrackingNumber分组，将奇偶行转为Date1/Date2列？

需求说明 #

需将#SourceData表的数据转换为#FinalData表格式，规则如下：

• 以TrackingNumber列为分组依据
• 按RowNumber列对日期进行排序
• RowNumber为奇数的日期放入Date1列，偶数的放入Date2列
• 支持单个TrackingNumber包含超过100条日期数据的场景

示例数据 #

源表（#SourceData） #

CREATE Table #SourceData 
(
    TrackingNumber int NULL,
    Date1 date NULL,
    Date2 date NULL,
    RowNumber int NULL
);

INSERT INTO #SourceData
      SELECT 3   , '09/18/2016', NULL, 1
UNION SELECT 3   , '12/21/2016', NULL, 2
UNION SELECT 12  , '01/30/2018', NULL, 1
UNION SELECT 12  , '01/30/2018', NULL, 2
UNION SELECT 12  , '03/01/2019', NULL, 3
UNION SELECT 12  , '03/05/2019', NULL, 4
UNION SELECT 12  , '04/19/2020', NULL, 5
UNION SELECT 23  , '02/14/2017', NULL, 1
UNION SELECT 130 , '04/12/2017', NULL, 1

目标表（#FinalData） #

CREATE Table #FinalData 
(
    TrackingNumber int NULL,
    Date1 date NULL,
    Date2 date NULL
);

INSERT INTO #FinalData
      SELECT 3   , '09/18/2016', '12/21/2016'
UNION SELECT 12  , '01/30/2018', '01/30/2018'
UNION SELECT 12  , '03/01/2019', '03/05/2019'
UNION SELECT 12  , '04/19/2020', NULL
UNION SELECT 23  , '02/14/2017', NULL
UNION SELECT 130 , '04/12/2017', NULL

解决方案代码 #

WITH PairedData AS (
    SELECT 
        TrackingNumber,
        Date1 AS OriginalDate,
        RowNumber,
        -- 计算配对组：每两行一组，奇数行与偶数行归为同一组
        CEILING(RowNumber / 2.0) AS PairGroup
    FROM #SourceData
)
SELECT 
    TrackingNumber,
    -- 提取组内奇数行的日期作为Date1
    MAX(CASE WHEN RowNumber % 2 = 1 THEN OriginalDate END) AS Date1,
    -- 提取组内偶数行的日期作为Date2
    MAX(CASE WHEN RowNumber % 2 = 0 THEN OriginalDate END) AS Date2
FROM PairedData
GROUP BY TrackingNumber, PairGroup
ORDER BY TrackingNumber, PairGroup;

方案说明 #

• 通过CEILING(RowNumber / 2.0)生成配对组，自动将连续的奇数、偶数行归为一组，无需硬编码处理行数上限
• 利用CASE表达式结合MAX聚合函数，分别提取每组内的奇数行日期（Date1）和偶数行日期（Date2）
• 支持任意数量的日期数据，即使单TrackingNumber超过100条也能正常处理

内容的提问来源于stack exchange，提问作者Jeff.Clark