Can this be written as a query instead of using a function?

Question

Hi folks,

I have a table in this format (similar to the class schedule you had in college):

create table match_times
(
  match_time     varchar2(20 char),
  match_sun_day  char(1 char),
  match_mon_day  char(1 char),
  match_tue_day  char(1 char),
  match_wed_day  char(1 char),
  match_thu_day  char(1 char),
  match_fri_day  char(1 char),
  match_sat_day  char(1 char)
)
comment on column match_times.match_time is 'E.g. ''08:00-08:50''';
comment on column match_times.match_sun_day is '''U'' or null';
comment on column match_times.match_mon_day is '''M'' or null';
comment on column match_times.match_tue_day is '''T'' or null';
comment on column match_times.match_wed_day is '''W'' or null';
comment on column match_times.match_thu_day is '''R'' or null';
comment on column match_times.match_fri_day is '''F'' or null';
comment on column match_times.match_sat_day is '''S'' or null';

I want to write a query that will get me for e.g.:

8:00 - 9:00 MTF
9:00 - 10:15 TR

This can be done easily using a function, but I'm curious if this can be done by using an SQL query. This isn't important, it's just for knowledge:)

EDIT: Sorry, I missed out one vital clarification. There can be multiple rows per match time. It could have MF on one row and W on the next. Now I understand why you folks were asking for the actual create table statement.

Answer 1

Yes, you can. But that is a terrible schema, making queries like "show me the total number of hours a professor is teaching this semester" very difficult.

Answer 2

Assuming that you only have one row per unique timespan (no primary key is listed):

SELECT
    match_time,
    COALESCE(match_sun_time, '') +
    COALESCE(match_mon_time, '') +
    COALESCE(match_tue_time, '') +
    COALESCE(match_wed_time, '') +
    COALESCE(match_thu_time, '') +
    COALESCE(match_fri_time, '') +
    COALESCE(match_sat_time, '')
FROM
    Match_Times

Answer 3

This should work:

with days AS (
  select
    match_time,
    max(coalesce(match_sun_day, '')) sun,
    max(coalesce(match_mon_day, '')) mon,
    max(coalesce(match_tue_day, '')) tue,
    max(coalesce(match_wed_day, '')) wed,
    max(coalesce(match_thu_day, '')) thu,
    max(coalesce(match_fri_day, '')) fri,
    max(coalesce(match_sat_day, '')) sat
  from
    match_times
  group by
    match_time
)
  select
    match_time + ' ' + sun+mon+tue+wed+thu+fri+sat
  from
    days

I don't have an install handy to test it. Worst case, if your DB doesn't order empty string before non-empty string for max, replace '' with 'a' and then put a case in for each day on the string building.