SQL how to create detail table from summary table

Question

Now I have a game summary table like this:

Summay Table

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

I need this

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

[What have you tried?](http://whathaveyoutried.com) If you post what you have tried, people will often be more inclined to help. We don't want to do people's job for them, but we will help if there's something in the SQL that you've tried that you are stuck on. — N West, Aug 30 '12 at 17:14
I've tried to use variables to calculate the number of times that one record may need to repeat. But I do not know how to insert the changed record automatically. Also, I need to remove the variable later because SSAS will not allow use of variables as T-SQL — Echo, Aug 30 '12 at 18:09

score 3 · Accepted Answer · edited Jun 13 '15 at 05:05

3

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

edited Jun 13 '15 at 05:05

potashin

44,205
11
83
107

answered Aug 30 '12 at 19:05

atiruz

2,782
27
36

Nico M · Answer 2 · 2012-08-30T18:15:47.037

If you're on SQL Server, you can try the following TSQL block. It uses a cursor to traverse those rows which have more than 1 hour between start and end times, and iterates through, adding the individual "hours" into a @gamesTable table variable.

Once the cursor is done and has populated records into the @gamesTable variable, a SELECT statement against the original table for those rows which have 1 or less hours between start and end times is unioned to all the rows that were stored into @gamesTable.

DECLARE Game_Cursor CURSOR FOR
    SELECT *
    FROM summaryTable
    WHERE EndTime - StartTime > 1

OPEN Game_Cursor;
DECLARE @PlayerID char
DECLARE @StartTime int
DECLARE @EndTime int
DECLARE @TempEndTime int
DECLARE @gamesTable TABLE(PlayerID char, StartTime int, EndTime int)

--Fetch the first row
FETCH NEXT FROM Game_Cursor INTO @PlayerID, @StartTime, @EndTime
WHILE @@FETCH_STATUS = 0
BEGIN
        WHILE (@EndTime - @StartTime) > 0
        BEGIN
            SET @TempEndTime = @StartTime + 1
            INSERT INTO @gamesTable
                SELECT @PlayerID AS PlayerID, @StartTime AS StartTime, @TempEndTime AS EndTime

            SET @StartTime = @StartTime + 1
        END;

        --Fetch the next row
        FETCH NEXT FROM Game_Cursor INTO @PlayerID, @StartTime, @EndTime
END;

--Rid of the cursor
CLOSE Game_Cursor;
DEALLOCATE Game_Cursor;

--Output the results
SELECT * FROM summaryTable WHERE (EndTime - StartTime) <= 1
UNION ALL
SELECT * FROM @gamesTable

[Why do people hate SQL cursors so much?](http://stackoverflow.com/questions/287445/why-do-people-hate-sql-cursors-so-much) — Kermit, Aug 30 '12 at 19:21
I think some people don't like cursors because they are at times used in situations where they are not the most efficient solution and waste resources. — Nico M, Sep 04 '12 at 22:42

Taryn · Answer 3 · 2013-01-31T19:17:12.313

In SQL Server 2005+ you can use CTE to get the results:

;with cte (playerid, starttime, endtime) as 
(
  select playerid, min(starttime) starttime, max(endtime) endtime
  from yourtable
  group by playerid
  union all
  select playerid, starttime + 1, endtime
  from cte
  where starttime < endtime
)
select playerid,
  starttime,
  case 
    when starttime + 1 < endtime
    then starttime + 1
    else endtime
  end endtime
from cte
where starttime != endtime
order by playerid, starttime

See SQL Fiddle with Demo

SQL how to create detail table from summary table

3 Answers3