SQL Server PIVOT函数应用及生产数据聚合优化技术问询

阿华AIGC实验室

2026-5-7

Solution for SQL Server PIVOT Optimization & New Columns

Let's break down your problem into manageable parts: fixing the filter logic, and adding the LastActiveStation and LastActiveTime columns. Here's a step-by-step implementation that should work for your scenario.

1. Filter Logic: Keep Relevant Work Orders

First, we need to isolate work orders that meet two criteria:

At least one station has a status of Started or Completed
The EndTime falls within the last 2 days

We can use a CTE (Common Table Expression) to first identify these valid work orders, then build our PIVOT on top of that subset.

2. Add LastActiveStation & LastActiveTime

Since your production flow follows S010→S120, the "last active station" would be the one with the most recent EndTime for each work order. We can use window functions like ROW_NUMBER() to tag the latest record per work order, then pull those values into our final result.

Full Query Example

WITH ValidWorkOrders AS (
    -- Step 1: Identify work orders that meet filter criteria
    SELECT DISTINCT WorkOrderID
    FROM YourProductionTable
    WHERE 
        EndTime >= DATEADD(day, -2, GETDATE()) -- Last 2 days from current time
        AND Status IN ('Started', 'Completed')
),
WorkOrderLatestActivity AS (
    -- Step 2: Get last active station and time for each valid work order
    SELECT 
        WorkOrderID,
        Station AS LastActiveStation,
        EndTime AS LastActiveTime,
        ROW_NUMBER() OVER (PARTITION BY WorkOrderID ORDER BY EndTime DESC) AS rn
    FROM YourProductionTable
    WHERE WorkOrderID IN (SELECT WorkOrderID FROM ValidWorkOrders)
),
PivotReadyData AS (
    -- Step 3: Combine valid data with last active info (repeat for each station row)
    SELECT 
        p.WorkOrderID,
        p.Station,
        p.Status,
        la.LastActiveStation,
        la.LastActiveTime
    FROM YourProductionTable p
    JOIN WorkOrderLatestActivity la 
        ON p.WorkOrderID = la.WorkOrderID AND la.rn = 1
    WHERE p.WorkOrderID IN (SELECT WorkOrderID FROM ValidWorkOrders)
)
-- Final PIVOT query
SELECT 
    WorkOrderID,
    LastActiveStation,
    LastActiveTime,
    -- Pivot columns for each sub-assembly station (adjust station codes to match your data)
    [S010], [S020], [S030], [S040], [S050], [S060], [S070],
    [S080], [S090], [S100], [S110], [S120], [S130], [S140]
FROM PivotReadyData
PIVOT (
    MAX(Status) -- Use MAX since each station has one status per work order
    FOR Station IN (
        [S010], [S020], [S030], [S040], [S050], [S060], [S070],
        [S080], [S090], [S100], [S110], [S120], [S130], [S140]
    )
) AS PivotTable
ORDER BY WorkOrderID;

Key Notes:

Filter Adjustment: If "last two days" means calendar days (e.g., today and yesterday, regardless of current time), replace DATEADD(day, -2, GETDATE()) with CAST(GETDATE() - 2 AS DATE).
Station Codes: Make sure the station codes in the FOR Station IN (...) list match exactly with your actual data (case-sensitive if your collation is set that way).
Handling NULLs: If some stations don't have data for a work order, the PIVOT will return NULL for those columns. You can wrap them in ISNULL() if you want to replace with a default like 'Not Started'.
Performance: If your table is large, adding indexes on WorkOrderID, EndTime, and Station will speed up the CTEs and PIVOT operation.