How to combine two rows of output of join query into one?

Question

I have two tables Slot and Timetable
Slot:

slotID|startTime|endTime
------------------------
1     |10:00:00|12:00:00
2     |13:00:00|15:00:00

Timetable:

ScheduleID|slotID|subjectID|day
-------------------------------
1         |1     |subject1 |mon
2         |2     |subject1 |mon
3         |1     |subject2 |tue
4         |2     |subject2 |tue

I tried

    SELECT slot.startTime, slot.endTime,
    (CASE WHEN day="mon" THEN timetable.subjectID END) as Monday,
    (CASE WHEN day="tue" THEN timetable.subjectID END) as Tuesday
    FROM timetable
    INNER JOIN slot ON timetable.slotID=slot.slotID

And I got:

startTime|endTime|Monday  |Tuesday
---------------------------------------
10:00:00|12:00:00|subject1|NULL
10:00:00|12:00:00|NULL    |subject2
13:00:00|15:00:00|subject1|NULL
13:00:00|15:00:00|NULL    |subject2

I want to write a SQL query to output 8 columns to print something like shown below

startTime |endTime |   mon  |   tue  |   wed  |   thu  |   fri  |   sat  
-------------------------------------------------------------------------
 10:00:00 |12:00:00|subject1|subject2|subject1|subject2|subject1|subject2
 13:00:00 |15:00:00|subject1|subject2|subject1|subject2|subject1|subject2

I want to remove the NULL values and combine the rows which have same startTime and endTime. Any help would be appreciated

Answer 1

You want aggregation:

SELECT s.startTime, s.endTime,
       MAX(CASE WHEN day = 'mon' THEN tt.subjectID END) as Monday,
       MAX(CASE WHEN day = 'tue' THEN tt.subjectID END) as Tuesday
FROM timetable tt INNER JOIN
     slot s
     ON tt.slotID = s.slotID
GROUP BY s.startTime, s.endTime;