如何将SQL单行多列结果转为带StartTime标签的单列多行结构

原查询用于提取7天内每日传送带的启动时间，当前返回单行结果，列名依次为StartTime1到StartTime7。需要将结果转换为多行结构，每行对应一个日期标识（如StartTime1）和对应的启动时间。

原查询代码：

Select 
    format(min(case when location = 0 and scantime between getdate()-1 and getdate() then scantime end),'hh:mm:ss:tt') [StartTime1]
    ,format(min(case when location = 0 and scantime between getdate()-2 and getdate()-1 then scantime end),'hh:mm:ss:tt') [StartTime2]
    ,format(min(case when location = 0 and scantime between getdate()-3 and getdate()-2 then scantime end),'hh:mm:ss:tt') [StartTime3]
    ,format(min(case when location = 0 and scantime between getdate()-4 and getdate()-3 then scantime end),'hh:mm:ss:tt') [StartTime4]
    ,format(min(case when location = 0 and scantime between getdate()-5 and getdate()-4 then scantime end),'hh:mm:ss:tt') [StartTime5]
    ,format(min(case when location = 0 and scantime between getdate()-6 and getdate()-5 then scantime end),'hh:mm:ss:tt') [StartTime6]
    ,format(min(case when location = 0 and scantime between getdate()-7 and getdate()-6 then scantime end),'hh:mm:ss:tt') [StartTime7]
    
FROM [BaldorFoods_WcsDb].[dbo].[ScanLog]
where datepart(hour,scantime) in (18,19,20,21,22,23,0,1,2,3,4,5,6)

解决方案 #

方法1：用UNPIVOT转置现有单行结果 #

直接基于原查询的输出进行转置，把列转为行：

SELECT DateLabel, StartTime
FROM (
    -- 原查询内容
    Select 
        format(min(case when location = 0 and scantime between getdate()-1 and getdate() then scantime end),'hh:mm:ss:tt') [StartTime1]
        ,format(min(case when location = 0 and scantime between getdate()-2 and getdate()-1 then scantime end),'hh:mm:ss:tt') [StartTime2]
        ,format(min(case when location = 0 and scantime between getdate()-3 and getdate()-2 then scantime end),'hh:mm:ss:tt') [StartTime3]
        ,format(min(case when location = 0 and scantime between getdate()-4 and getdate()-3 then scantime end),'hh:mm:ss:tt') [StartTime4]
        ,format(min(case when location = 0 and scantime between getdate()-5 and getdate()-4 then scantime end),'hh:mm:ss:tt') [StartTime5]
        ,format(min(case when location = 0 and scantime between getdate()-6 and getdate()-5 then scantime end),'hh:mm:ss:tt') [StartTime6]
        ,format(min(case when location = 0 and scantime between getdate()-7 and getdate()-6 then scantime end),'hh:mm:ss:tt') [StartTime7]
    FROM [BaldorFoods_WcsDb].[dbo].[ScanLog]
    where datepart(hour,scantime) in (18,19,20,21,22,23,0,1,2,3,4,5,6)
) AS SourceTable
UNPIVOT (
    StartTime FOR DateLabel IN (
        [StartTime1], [StartTime2], [StartTime3], [StartTime4], [StartTime5], [StartTime6], [StartTime7]
    )
) AS UnPivotTable;

方法2：重构查询（更高效简洁） #

原查询的case语句重复冗余，可先按日期分组计算每天的启动时间，再生成对应的StartTimeN标识，逻辑更清晰：

SELECT 
    'StartTime' + CAST(ROW_NUMBER() OVER(ORDER BY DayDate DESC) AS VARCHAR(2)) AS DateLabel,
    FORMAT(MinScanTime, 'hh:mm:ss:tt') AS StartTime
FROM (
    SELECT 
        -- 获取当天的起始时间（避免getdate()随查询执行时间变动的问题）
        DATEADD(DAY, DATEDIFF(DAY, 0, scantime), 0) AS DayDate,
        MIN(scantime) AS MinScanTime
    FROM [BaldorFoods_WcsDb].[dbo].[ScanLog]
    WHERE 
        location = 0
        AND datepart(hour, scantime) IN (18,19,20,21,22,23,0,1,2,3,4,5,6)
        -- 筛选最近7天的数据
        AND scantime >= DATEADD(DAY, -7, GETDATE())
    GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, scantime), 0)
) AS DailyScan
ORDER BY DayDate DESC;

这个方法的优势：

• 避免重复编写case语句，维护更方便
• 用DATEADD(DAY, DATEDIFF(DAY, 0, scantime), 0)精准获取日期部分，避免getdate()的时间差误差
• 自动按日期排序，生成对应的StartTime1到StartTime7标识

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