SQL Server 2014如何按FLOW_STEP_ID将多行数据整理为新行

针对SQL Server 2014的场景，我们可以通过窗口函数分组+条件聚合实现需求，核心是先为每个订单组和拣选循环建立标识，再将分散的步骤数据聚合为一行。

步骤说明 #

1. 标记订单组：同一个JOB_ID内，从FLOW_STEP_ID=302（订单号）开始，到下一个302步骤或JOB_ID变更前的所有步骤，归为同一个订单组。
2. 标记拣选循环：每个订单组内，重复的134-97-99-136步骤序列为一个拣选循环，用FLOW_STEP_ID=134作为循环起始标记。
3. 聚合转列：针对每个订单组+拣选循环，将对应步骤的SCANNED_DATA映射到目标列，并保留操作时间。

完整SQL代码 #

WITH OrderGroups AS (
    -- 第一步：为每条记录标记所属的订单组（JOB_ID内累计出现的302步骤数）
    SELECT 
        ID,
        JOB_ID,
        FLOW_STEP_ID,
        SCANNED_DATA,
        AUDIT_TIME,
        COUNT(CASE WHEN FLOW_STEP_ID = 302 THEN 1 END) OVER (
            PARTITION BY JOB_ID 
            ORDER BY AUDIT_TIME, ID 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS OrderGroupID
    FROM 你的原表名 -- 替换为实际表名
),
CycleGroups AS (
    -- 第二步：为每个订单组内的拣选循环标记编号（以134步骤为循环起始）
    SELECT 
        *,
        COUNT(CASE WHEN FLOW_STEP_ID = 134 THEN 1 END) OVER (
            PARTITION BY JOB_ID, OrderGroupID 
            ORDER BY AUDIT_TIME, ID 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS CycleID
    FROM OrderGroups
)
-- 第三步：条件聚合生成目标表
SELECT 
    JOB_ID,
    MAX(CASE WHEN FLOW_STEP_ID = 302 THEN SCANNED_DATA END) AS SALES_ORDER,
    MAX(CASE WHEN FLOW_STEP_ID = 134 THEN SCANNED_DATA END) AS BIN,
    MAX(CASE WHEN FLOW_STEP_ID = 97 THEN SCANNED_DATA END) AS STOCK_CODE,
    MAX(CASE WHEN FLOW_STEP_ID = 99 THEN SCANNED_DATA END) AS LOT,
    MAX(CASE WHEN FLOW_STEP_ID = 136 THEN SCANNED_DATA END) AS QTY,
    MAX(CASE WHEN FLOW_STEP_ID = 136 THEN AUDIT_TIME END) AS AUDIT_TIME
FROM CycleGroups
WHERE FLOW_STEP_ID IN (302,134,97,99,136)
GROUP BY JOB_ID, OrderGroupID, CycleID
-- 可选：过滤未完成的拣选循环（比如只有货位扫描无数量的记录）
HAVING MAX(CASE WHEN FLOW_STEP_ID = 136 THEN 1 END) IS NOT NULL
ORDER BY JOB_ID, OrderGroupID, CycleID;

关键说明 #

• OrderGroupID：确保同一个JOB_ID内，后续步骤会继承最近一次的订单号，直到下一个302步骤出现。
• CycleID：将重复的拣选步骤序列分组，保证每个拣选动作对应一行数据。
• HAVING子句：可按需保留/过滤未完成的拣选循环，去掉则会显示所有扫描过的步骤组合。

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