I have a problem that I can't solve myself.
I have the following table in Mysql (result of some join) that describes all the events
event_table:
| ID | Odl_ID | Evt_ID | Evt_Desc | Evt_Date | Link_1 | Link_2 | Start_Time | End_Time |
|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 0 | Start | 2021-08-06 10:15:00 | Null | Null | 2021-08-06 10:15:00 | |
| 2 | 2 | 0 | Start | 2021-08-06 12:15:00 | Null | Null | 2021-08-06 12:18:00 | |
| 3 | 1 | 0 | Finish | 2021-08-06 14:15:00 | Null | Null | 2021-08-06 10:15:00 | 2021-08-06 14:15:00 |
| 4 | 1 | 0 | Generate PDF 1 | 2021-08-06 14:25:00 | C:\Path1 | Null | ||
| 5 | 1 | 0 | Generate PDF 2 | 2021-08-06 14:25:00 | Null | C:\Path2 | ||
| 6 | 2 | 0 | Finish | 2021-08-06 14:35:00 | Null | Null | 2021-08-06 10:18:00 | 2021-08-06 18:15:00 |
| 7 | 2 | 0 | Generate PDF 1 | 2021-08-06 18:25:00 | C:\Path3 | Null | ||
| 8 | 2 | 0 | Generate PDF 2 | 2021-08-06 18:25:00 | Null | C:\Path4 |
And I want to create a view like this that group the Odl_ID and present all the events found in different rows (like the event date, Link_1, etc).
The resulting table that I'm tring to achive:
| ID | Odl_ID | Evt_Date | Link_1 | Link_2 | Start_Time | End_Time |
|---|---|---|---|---|---|---|
| 1 | 1 | 2021-08-06 10:15:00 | C:\Path1 | C:\Path2 | 2021-08-06 10:15:00 | 2021-08-06 14:15:00 |
| 2 | 2 | 2021-08-06 12:15:00 | C:\Path3 | C:\Path4 | 2021-08-06 12:18:00 | 2021-08-06 18:15:00 |
How can I solve the problem? I can't figure out how to do it, I tried looking for pivot table but I haven't figured out the problem yet. Thanks
Found a solution that might work, but don't know if it's fully correct / maybe there's a better solution for that
SELECT Odl_Id,
min(Start_time) Start_time,
max(End_time) End_time,
max(Evt_ID) Evt_ID,
max(Link_1) Link_1,
max(Link_2) Link_2
FROM view_2
Group by ODL_Id;