SQL Query Group Like Values within the same row and concatenate and sort others

Question

Using SQL Version 15.0.2000.5

I have a table with this data:

StudentScheduleId	Monday	Tuesday	Wednesday	Thursday	Friday	MondayStartTime	MondayEndTime	TuesdayStartTime	TuesdayEndTime	WednesdayStartTime	WednesdayEndTime	ThursdayStartTime	ThursdayEndTime	FridayStartTime	FridayEndTime
15	1	1	1	1	NULL	9:00	11:00	11:00	12:30	9:00	11:00	11:00	12:30	NULL	NULL
31	1	NULL	NULL	1	0	2:00	3:15	NULL	NULL	NULL	NULL	2:00	3:15	NULL	NULL

I want to achieve this format:

StudentScheduleId	Schedule	StartTime	EndTime
15	T/Th	11:00	12:30
15	M/W	9:00	11:00
31	M	9:00	11:00

I was able to do this using this query:

Select s.StudentScheduleId, 
    STRING_AGG(s.[Day], '/') AS Schedule, 
    s.StartTime, 
    s.EndTime
From
(
(Select s.StudentScheduleId, 'M' As [Day], s.MondayStartTime As StartTime, s.MondayEndTime As EndTime
From StudentSchedule s
Where s.Monday = 1)
UNION
(Select s.StudentScheduleId, 'T' As [Day], s.TuesDayStartTime As StartTime, s.TuesdayEndTime As EndTime
From StudentSchedule s
Where s.Tuesday = 1)
UNION
(Select s.StudentScheduleId, 'W' As [Day], s.WednesdayStartTime As StartTime, s.WednesdayEndTime As EndTime
From StudentSchedule s
Where s.Wednesday = 1)
UNION
(Select s.StudentScheduleId, 'Th' As [Day], s.ThursdayStartTime As StartTime, s.ThursdayEndTime As EndTime
From StudentSchedule s
Where s.Thursday = 1)
UNION
(Select s.StudentScheduleId, 'F' As [Day], s.FridayStartTime As StartTime, s.FridayEndTime As EndTime
From StudentSchedule s
Where s.Friday = 1)
) As s
Group By s.StudentScheduleId, s.StartTime, s.EndTime

However, I have one concern and also one issue with the results.

1. Is there a better more proficient way of doing this considering performance or just being more succinct? I don't expect this table getting to millions records, but I could see it growing into 100's of thousands eventually.
2. I tested other scenarios using more days of the week having the same start and end time and the schedule column result can come out in any order. (Example: F/M/T/Th/W). I would want this to come out in logical order M/T/W/Th/F. I know about the WITHIN GROUP sorting for STRING_AGG but there's nothing to sort on or I'd need to add a sort column, any ideas?

Thank You for any help!

Answer 1

A slightly shorter solution for you:

SELECT  *
INTO #data
FROM    (
    VALUES  (15, 1, 1, 1, 1, NULL, N'9:00', N'11:00', N'11:00', N'12:30', N'9:00', N'11:00', N'11:00', N'12:30', cast(NULL AS nvarchar(10)), cast(NULL AS nvarchar(10)))
    ,   (31, 1, NULL, NULL, 1, 0, N'2:00', N'3:15', NULL, NULL, NULL, NULL, N'2:00', N'3:15', NULL, NULL)
) t (StudentScheduleId,Monday,Tuesday,Wednesday,Thursday,Friday,MondayStartTime,MondayEndTime,TuesdayStartTime,TuesdayEndTime,WednesdayStartTime,WednesdayEndTime,ThursdayStartTime,ThursdayEndTime,FridayStartTime,FridayEndTime)

SELECT  StudentScheduleId, starttime, endtime, STRING_AGG(shortcode, '/') WITHIN GROUP (ORDER BY daynumber)
FROM    #data d
CROSS APPLY (
    VALUES  (1,'M',monday, mondaystarttime, mondayendtime)
    ,   (2,'T',tuesday, tuesdaystarttime, tuesdayendtime)
    ,   (3,'W',Wednesday, WednesdayStartTime, WednesdayEndTime)
    ,   (4,'Th',Thursday, ThursdayStartTime, ThursdayEndTime)
    ,   (5,'F',Friday, FridayStartTime, FridayEndTime)
    ) v (daynumber, shortcode, day, starttime, endtime)
WHERE   v.day = 1
GROUP BY starttime, endtime,StudentScheduleId
ORDER BY StudentScheduleId, MIN(daynumber)

You can unpivot the days and times into rows which simplifies the aggregation a lot.