I have a table in SQL Server 2016 that holds the scheduling of meetings across the weekdays. It stores the days in a column for each day of the week (examples below). I am wanting to query this table and get the results in a single column in a more user-friendly format.
Currently the data is stored in the table like this:
SCHEDULE TABLE:
MEETING_TITLE | MONDAY | TUESDAY | WEDNESDAY | THURSDAY | FRIDAY
FOO | NULL | Y | NULL | Y | NULL
My desire is to combine these weekday columns in the results, and convert them to the abbreviation for the respective days.
Desired result:
SCHEDULE TABLE:
MEETING_TITLE | MEETING_DAYS
FOO | T/THU
I have looked into case statements, and if/else and have tried to work with declaring variables to hold the values, but nothing has worked so far. To demonstrate my attempt at solving I specifically tried to declare a variable to hold the formatted days. After that I tried to do an if/else for each day looking for 'Y' in the column. If found, I hoped to do a += kind of thing to combine the values as needed. Everything I tried just resulted in errors and the query wouldn't run.
I appreciate any help on this.
(The above example is of course simplified, this table is far more complex than the above)
--You can use CASE Statements to concat and then
--a combination of STUFF and REVERSE to remove the trailing / off [MEETING_DAYS]
SELECT [MEETING_TITLE], reverse(stuff(reverse(
CASE WHEN [Monday] IS NULL THEN '' ELSE 'Mon/' END +
CASE WHEN [Tuesday] IS NULL THEN '' ELSE 'T/' END +
CASE WHEN [Wednesday] IS NULL THEN '' ELSE 'Wed/' END +
CASE WHEN [Thursday] IS NULL THEN '' ELSE 'THU/' END +
CASE WHEN [Friday] IS NULL THEN '' ELSE 'Fri/' END), 1, 1, '')) AS [MEETING_DAYS]
FROM [dbo].[Schedule]