Can a Pivot Resolve this?-CodePudding

I'm using SQL Server 2016

I have a table with the locations, start times and days of the week for an Event. 3 columns: Location, StartTime and DayofWeek. The Locations can have the Event on any DayofWeek and one or more that one StartTime on a Day or no StartTimes on Day.

enter description here

The Result table I need should have 8 Columns: Location, MonTime, TueTime, WedTime, ThuTime, FriTime, SatTime, SunTime.If there is more than one time on one or more days for a Location, there will be a row per different StartTime against that Location.

enter description here

I have tried a Pivot with Max and Min which sort of resolves the problem but only if there is a max of 2 different times on a day.

I also tried a Cursor loop with INSERTS and dynamic UPDATEs but that was a failure.

Any suggestions would be gratefully received. Thanks

CodePudding user response：

You can do it by first assigning a ranking to each of your "StartTime" partitioned by "Location" and "DayofWeek". Then you can easily apply your pivot with the CASE statements and the aggregation on both your "Location" and just computed ranking number:

WITH cte AS (
    SELECT *,
           ROW_NUMBER() OVER(PARTITION BY Location, DayofWeek ORDER BY StartTime) AS rn
    FROM tab
)
SELECT Location AS Site,
       MAX(CASE WHEN DayofWeek='Mon' THEN StartTime END) AS MonTime,
       MAX(CASE WHEN DayofWeek='Tue' THEN StartTime END) AS TueTime,
       MAX(CASE WHEN DayofWeek='Wed' THEN StartTime END) AS WedTime,
       MAX(CASE WHEN DayofWeek='Thu' THEN StartTime END) AS ThuTime,
       MAX(CASE WHEN DayofWeek='Fri' THEN StartTime END) AS FriTime,
       MAX(CASE WHEN DayofWeek='Sat' THEN StartTime END) AS SatTime,
       MAX(CASE WHEN DayofWeek='Sun' THEN StartTime END) AS SunTime
FROM cte
GROUP BY Location, 
         rn
ORDER BY Site, 
         MonTime

Check the demo