使用Cross Apply和Pivot实现SQL订单状态时间分段转换

核心思路 #

因为部件数量不固定，无法硬编码列名，必须用动态SQL+Pivot组合实现：先通过CROSS APPLY将原表多时间字段拆为行数据，再动态生成所有需要的列名，最后执行动态Pivot完成转换。

完整实现代码 #

假设原表名为CoreOrderStatus，以下是可直接运行的代码：

DECLARE @Columns NVARCHAR(MAX), @SQL NVARCHAR(MAX)

-- 获取所有唯一的[PartNum]_[ID]列名（SQL Server 2017+可用STRING_AGG）
SELECT @Columns = STRING_AGG(QUOTENAME(CONCAT(PartNum, '_', ID)), ', ')
FROM (
    SELECT DISTINCT PartNum, ID
    FROM CoreOrderStatus
) t

-- 兼容SQL Server 2016及以下版本的列名生成方式（替换上面的STRING_AGG）
-- SELECT @Columns = STUFF((
--     SELECT ', ' + QUOTENAME(CONCAT(PartNum, '_', ID))
--     FROM (SELECT DISTINCT PartNum, ID FROM CoreOrderStatus) t
--     FOR XML PATH(''), TYPE
-- ).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

-- 拼接动态Pivot执行语句
SET @SQL = N'
SELECT t_stamp, ' + @Columns + '
FROM (
    -- 第一步：用CROSS APPLY将多时间字段拆为行数据
    SELECT 
        t_stamp = CASE 
            WHEN s.StatusType = ''ENTERED'' THEN c.EnteredOn
            WHEN s.StatusType = ''PICKED'' THEN c.PickedTime
            WHEN s.StatusType = ''DELIVERED'' THEN c.DeliveredTime
        END,
        ColumnName = CONCAT(c.PartNum, ''_'', c.ID),
        StatusValue = s.StatusType
    FROM CoreOrderStatus c
    CROSS APPLY (
        VALUES 
            (''ENTERED''),
            (''PICKED''),
            (''DELIVERED'')
    ) s(StatusType)
    -- 过滤无有效时间的状态记录
    WHERE CASE 
            WHEN s.StatusType = ''ENTERED'' THEN c.EnteredOn
            WHEN s.StatusType = ''PICKED'' THEN c.PickedTime
            WHEN s.StatusType = ''DELIVERED'' THEN c.DeliveredTime
        END IS NOT NULL
) src
-- 第二步：Pivot将行转列
PIVOT (
    MAX(StatusValue)
    FOR ColumnName IN (' + @Columns + ')
) pvt
ORDER BY t_stamp
'

-- 执行动态SQL
EXEC sp_executesql @SQL

关键细节说明 #

• 拆行逻辑：CROSS APPLY VALUES把每个订单的3个状态（ENTERED/PICKED/DELIVERED）拆成独立行，同时绑定对应的时间戳和列名标识。
• 动态列名：通过DISTINCT获取所有唯一的PartNum_ID组合，用STRING_AGG或XML拼接成Pivot需要的列列表，完全避免硬编码。
• Pivot聚合：用MAX(StatusValue)是因为每个t_stamp+ColumnName组合只会有一条有效状态记录，聚合函数仅满足Pivot语法要求，不影响最终结果。
• 过滤无效记录：通过WHERE子句排除没有对应时间的状态，避免生成空时间戳的无效行。

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