I have a table
SELECT
CONVERT(VARCHAR(5),T.HORAINI_AULA,108) as HORAINI, reservation, day_week
FROM
vw_turmas_grade T
| HORAINI | reservation | day_week |
|---|---|---|
| 19:15 | P1INF.6 | 2 |
| 20:45 | P1INF.6 | 2 |
| 08:15 | PJB2/4 | 2 |
| 09:45 | PJB2/4 | 2 |
| 08:15 | PJB2/4 | 5 |
| 09:45 | PJB2/4 | 5 |
| 11:00 | PJB2/4 | 5 |
| 19:15 | PHC2/4 | 4 |
| 20:45 | PHC2/4 | 4 |
| 19:15 | PHC2/4 | 5 |
| 20:45 | PHC2/4 | 5 |
| 19:15 | PHC2/4 | 2 |
| 20:45 | PHC2/4 | 2 |
| 19:15 | PHC2/4 | 3 |
| 20:45 | PHC2/4 | 3 |
| 19:15 | PJB8 | 2 |
that returns the time and day of the week of the reservation and I would like to create a table of the week with these reservations.
notes: day_week 2 = monday, 3 = tuesday and etc...
Some like this:
| HORAINI | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday |
|---|---|---|---|---|---|---|
| 1900-01-01 08:15:00.000 | NULL | NULL | NULL | NULL | NULL | NULL |
| 1900-01-01 09:45:00.000 | NULL | NULL | NULL | NULL | NULL | NULL |
| 1900-01-01 11:00:00.000 | NULL | NULL | NULL | NULL | NULL | NULL |
| 1900-01-01 13:15:00.000 | NULL | NULL | NULL | NULL | NULL | NULL |
| 1900-01-01 14:15:00.000 | NULL | NULL | NULL | NULL | NULL | NULL |
| 1900-01-01 14:45:00.000 | NULL | NULL | NULL | NULL | NULL | NULL |
| 1900-01-01 15:45:00.000 | NULL | NULL | NULL | NULL | NULL | NULL |
| 1900-01-01 19:15:00.000 | NULL | NULL | NULL | NULL | NULL | NULL |
| 1900-01-01 20:45:00.000 | NULL | NULL | NULL | NULL | NULL | NULL |
| 1900-01-01 22:00:00.000 | NULL | NULL | NULL | NULL | NULL | NULL |
EDIT: Please ignore the date. Booking times are the same every day
To be honest, I think I'm making some silly mistake. Already tried a LEFT JOIN and CASEs to try this and not worked.
like:
WITH Reservation AS
(SELECT
CONVERT(VARCHAR(5),T.HORAINI_AULA,108) as HORAINI,
reservation,
day_week
FROM
vw_turmas_grade T)
SELECT
HORAINI,
CASE WHEN R.day_week = '2' THEN R.reservation END as 'Monday'
CASE WHEN R.day_week = '3' THEN R.reservation END as 'Tuesday'
CASE WHEN R.day_week = '4' THEN R.reservation END as 'Wednesday'
CASE WHEN R.day_week = '5' THEN R.reservation END as 'Thursday'
CASE WHEN R.day_week = '6' THEN R.reservation END as 'Friday'
CASE WHEN R.day_week = '7' THEN R.reservation END as 'Saturday'
FROM Reservation R
| HORAINI | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday |
|---|---|---|---|---|---|---|
| 19:15 | P1INF.6 | NULL | NULL | NULL | NULL | NULL |
| 20:45 | P1INF.6 | NULL | NULL | NULL | NULL | NULL |
| 19:15 | NULL | NULL | P1INF.7 | NULL | NULL | NULL |
| 20:45 | NULL | NULL | P1INF.7 | NULL | NULL | NULL |
| 19:15 | NULL | NULL | NULL | P1INF.7 | NULL | NULL |
| 20:45 | NULL | NULL | NULL | P1INF.7 | NULL | NULL |
| 19:15 | NULL | P1INF.7 | NULL | NULL | NULL | NULL |
| 20:45 | NULL | P1INF.7 | NULL | NULL | NULL | NULL |
| 19:15 | NULL | NULL | NULL | NULL | P1INF.7 | NULL |
| 20:45 | NULL | NULL | NULL | NULL | P1INF.7 | NULL |
But the table is wrong and the times that do not have a reservation do not appear
