SQL how to create detail table from summary table

Question

Now I have a game summary table like this:

And I want to split the "starttime" and "endtime" by hours and to convert it into following detailed look:

It's kind of similar as Unpivot but not exactly the same. How should I write the sql statement? Thanks a tons!!

Answer 1

In PostgreSQL is:

SELECT PlayerID
     , generate_series(s.StartTime, s.EndTime - 1) AS StartTimeCol
     , generate_series(s.StartTime + 1, s.EndTime) AS EndTimeCol
FROM summaryTable s

In this Link you can see how to add generate_series function to SQL Server 2008.

View in action Here