Pivot Table with 1 Row to 1 Column

I have a query that outputs daily conveyor start time over a span of 7 days using the following query:

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)

This outputs table in the image: MyTable. How would I go about changing this table so that the row names are labeled StartTime1, StartTime2, StartTime3.... etc.

I have tried using the Pivot table function but I am having difficulty specifying the inputs for the function.

Solution

I'd approach this somewhat differently

select format(scantime, 'hh:mm:ss:tt')
from (values
  (0),
  (1),
  (2),
  (3),
  (4),
  (5),
  (6)
) as up(n)
cross apply (
  select top(1) scantime
  from [BaldorFoods_WcsDb].[dbo].[ScanLog]
  where datepart(hour,scantime) in (18,19,20,21,22,23,0,1,2,3,4,5,6)
    and scantime between getdate() - up.n and getdate() - (up.n-1)
  order by scantime
) as d;

By way of explanation, let's break it down. Here's a simpler version that just gets you just the date boundaries:

select n, getdate() - up.n, getdate() - (up.n-1)
from (values
  (0),
  (1),
  (2),
  (3),
  (4),
  (5),
  (6)
) as up(n)

From there, we use the cross apply to get the first row (as ordered by your scantime column) that falls within a given set of date boundaries and has the predicate on the hour portion also applied.