Vertically align a table using SQL
My storyline is probably not the best, but here's what I'm trying to do:
I have a table with start and end times:
Event Start End
-------------------------
event1 2:30 PM 3:00 PM
event2 3:05 PM 3:30 PM
event3 2:45 PM 3:10 PM
I want to write a SQL command (in Oracle) that will result in:
Event Type Time
-------------------------
event1 START 2:30 PM
event3 START 2:45 PM
event1 END 3:00 PM
event2 START 3:05 PM
event3 END 3:10 PM
event2 END 3:30 PM
Basically, I need to order the time and know its event, as well as the start or end time. I have a feeling this can be achieved with some fancy SQL, but I'm having trouble how to do it.
Thank!
+3
source to share
1 answer
Nothing fancy but just UNION
enough
SELECT Event, 'START', Start AS Time
FROM ATable
UNION ALL
SELECT Event, 'END', End AS Time
FROM ATable
ORDER BY
Time
If you like it a little more, UNPIVOT
this is another option
SELECT Event, Type, Time
FROM ATable
UNPIVOT (Time FOR Type IN (Start, [End])) AS unpvt
ORDER BY
Time
+5
source to share