CODEWITHSUNDEEP
sqloracle-database
Paul
Paul

Reputation: 3954

Vertically un-flatten table using SQL

My subject line 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) which 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 times and know the event it's for and whether it's the start or end time. I have a feeling this can be accomplished with some fancy SQL, but I'm having trouble thinking of how to do it.

Thanks!

Upvotes: 3

Views: 1537

Answers (1)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

Nothing fancy but a simple UNION would suffice

SELECT Event, 'START', Start AS Time
FROM   ATable
UNION ALL
SELECT Event, 'END', End AS Time
FROM   ATable
ORDER BY
       Time

If you like to get a bit more fancy, UNPIVOT is another option

SELECT  Event, Type, Time
FROM    ATable
UNPIVOT (Time FOR Type IN (Start, [End])) AS unpvt
ORDER BY  
        Time

Upvotes: 8

Related Questions