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
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;