如何基于EmpID将多组Start_Date和End_Date进行Pivot转换？

核心思路 #

要同时将每个EmpID对应的多组起止日期转成单行，关键是先给每个EmpID下的任务按顺序编号，再通过条件聚合或动态SQL把每组Start/End配对转成对应的列。

步骤1：给任务生成序号 #

先通过窗口函数ROW_NUMBER()为每个EmpID的任务分配唯一序号（按TaskID排序，也可根据实际需求更换排序字段）：

SELECT 
    EmpID,
    Start_Date,
    End_Date,
    ROW_NUMBER() OVER(PARTITION BY EmpID ORDER BY TaskID) AS TaskSeq
FROM YourTaskTable

方法1：条件聚合（通用所有支持窗口函数的数据库） #

如果能确定最大任务数量（比如最多10条），直接用CASE语句按序号筛选对应字段，再聚合：

SELECT
    EmpID,
    -- 第1组任务
    MAX(CASE WHEN TaskSeq = 1 THEN Start_Date END) AS Start1,
    MAX(CASE WHEN TaskSeq = 1 THEN End_Date END) AS End1,
    -- 第2组任务
    MAX(CASE WHEN TaskSeq = 2 THEN Start_Date END) AS Start2,
    MAX(CASE WHEN TaskSeq = 2 THEN End_Date END) AS End2,
    -- 按需扩展到最大任务数，比如到第10组
    MAX(CASE WHEN TaskSeq = 10 THEN Start_Date END) AS Start10,
    MAX(CASE WHEN TaskSeq = 10 THEN End_Date END) AS End10
FROM (
    SELECT 
        EmpID,
        Start_Date,
        End_Date,
        ROW_NUMBER() OVER(PARTITION BY EmpID ORDER BY TaskID) AS TaskSeq
    FROM YourTaskTable
) AS TaskWithSeq
GROUP BY EmpID

这里用MAX是因为每个序号对应唯一一条记录，聚合后只会保留对应的值，MIN也能达到同样效果。

方法2：动态SQL（适配任务数量不固定的场景） #

如果任务数量不固定，用动态SQL自动生成所有需要的列，以下是不同数据库的实现：

SQL Server版本

-- 获取最大任务序号
DECLARE @MaxSeq INT
SELECT @MaxSeq = MAX(TaskSeq)
FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY EmpID ORDER BY TaskID) AS TaskSeq
    FROM YourTaskTable
) AS Seq

-- 生成列定义
DECLARE @Cols NVARCHAR(MAX) = ''
SELECT @Cols = @Cols + 
    ', MAX(CASE WHEN TaskSeq = ' + CAST(Seq AS VARCHAR) + ' THEN Start_Date END) AS Start' + CAST(Seq AS VARCHAR) +
    ', MAX(CASE WHEN TaskSeq = ' + CAST(Seq AS VARCHAR) + ' THEN End_Date END) AS End' + CAST(Seq AS VARCHAR)
FROM (
    SELECT DISTINCT TaskSeq
    FROM (
        SELECT ROW_NUMBER() OVER(PARTITION BY EmpID ORDER BY TaskID) AS TaskSeq
        FROM YourTaskTable
    ) AS Seq
) AS DistinctSeq
ORDER BY Seq

-- 拼接并执行最终SQL
DECLARE @FinalSQL NVARCHAR(MAX) = '
SELECT EmpID' + @Cols + '
FROM (
    SELECT 
        EmpID,
        Start_Date,
        End_Date,
        ROW_NUMBER() OVER(PARTITION BY EmpID ORDER BY TaskID) AS TaskSeq
    FROM YourTaskTable
) AS TaskWithSeq
GROUP BY EmpID
'

EXEC sp_executesql @FinalSQL

MySQL版本

-- 获取最大任务序号
SELECT MAX(TaskSeq) INTO @MaxSeq
FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY EmpID ORDER BY TaskID) AS TaskSeq
    FROM YourTaskTable
) AS Seq;

-- 生成列定义
SET @Cols = '';
SELECT GROUP_CONCAT(
    CONCAT(
        'MAX(CASE WHEN TaskSeq = ', Seq, ' THEN Start_Date END) AS Start', Seq, ',',
        'MAX(CASE WHEN TaskSeq = ', Seq, ' THEN End_Date END) AS End', Seq
    )
) INTO @Cols
FROM (
    SELECT DISTINCT TaskSeq
    FROM (
        SELECT ROW_NUMBER() OVER(PARTITION BY EmpID ORDER BY TaskID) AS TaskSeq
        FROM YourTaskTable
    ) AS Seq
) AS DistinctSeq
ORDER BY Seq;

-- 拼接并执行最终SQL
SET @FinalSQL = CONCAT('
SELECT EmpID, ', @Cols, '
FROM (
    SELECT 
        EmpID,
        Start_Date,
        End_Date,
        ROW_NUMBER() OVER(PARTITION BY EmpID ORDER BY TaskID) AS TaskSeq
    FROM YourTaskTable
) AS TaskWithSeq
GROUP BY EmpID
');

PREPARE stmt FROM @FinalSQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

动态SQL会自动根据实际存在的任务序号生成对应的StartN和EndN列，无需手动维护列数量。

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