Populating one SQL Server table with values ​​from another

Question

I have a scoresByMinute table that shows the score when a goal is scored in football

 matchID minute scoreline 1 24 10 1 53 20 1 81 21 2 7 01 ... 

I want to create a new table that shows the score for every minute of every 90 minute game

 matchID minute scoreline 1 1 00 1 2 00 ... 1 23 00 1 24 01 1 25 01 ... 1 89 21 1 90 21 2 1 00 

etc.

How can I do it?

Answer 1

 ;WITH scoresByMinute (matchID, minute, scoreline) AS ( SELECT 1, 24, '10' UNION ALL SELECT 1, 53, '20' UNION ALL SELECT 1, 81, '21' UNION ALL SELECT 2, 7, '01' ), maxMins AS ( SELECT matchID, maxMin = MAX(minute) FROM scoresByMinute GROUP BY matchID ), allminutes AS ( SELECT m.matchID, minute = v.number, scoreline = s.scoreline FROM maxMins m INNER JOIN master..spt_values v ON v.type = 'P' AND v.number BETWEEN 1 AND CASE WHEN m.maxMin < 90 THEN 90 ELSE m.maxMin END LEFT JOIN scoresByMinute s ON m.matchID = s.matchID and v.number = s.minute ), filledscorelines AS ( SELECT matchID, minute, scoreline = COALESCE(scoreline, '00') FROM allminutes WHERE minute = 1 UNION ALL SELECT m.matchID, m.minute, scoreline = COALESCE(m.scoreline, s.scoreline) FROM allminutes m INNER JOIN filledscorelines s ON m.matchID = s.matchID AND m.minute = s.minute + 1 ) SELECT * FROM filledscorelines ORDER BY matchID, minute 

Answer 2

Create a new table with the structure you want and then run for each match

 declare @counter int declare @scoreline varchar(10) declare @matchID int set @counter = 1 set @matchID = 1 set @scoreline = '00' while (@counter <= 90) begin select @scoreline = ISNULL(scoreline,@scoreline) from scores where minute = @counter insert into filledScoreLines(matchID, minute, scoreline) select @matchID as matchID, @counter as min, @scoreline as scoreline set @counter = @counter + 1 end
declare @counter int declare @scoreline varchar(10) declare @matchID int set @counter = 1 set @matchID = 1 set @scoreline = '00' while (@counter <= 90) begin select @scoreline = ISNULL(scoreline,@scoreline) from scores where minute = @counter insert into filledScoreLines(matchID, minute, scoreline) select @matchID as matchID, @counter as min, @scoreline as scoreline set @counter = @counter + 1 end 

To do this for multiple matches, simply flip all the match IDs that you have:

 declare @matchID int declare getEm cursor local for select distinct matchID from scoresByMinute open getEm while (1=1) begin fetch next from getEm into @matchID if (@@fetch_status 0) begin DEALLOCATE getEm break end declare @counter int declare @scoreline varchar(10) set @counter = 1 set @scoreline = '00' while (@counter <= 90) begin select @scoreline = ISNULL(scoreline,@scoreline) from scores where minute = @counter insert into filledScoreLines(matchID, minute, scoreline) select @matchID as matchID, @counter as min, @scoreline as scoreline set @counter = @counter + 1 end end
declare @matchID int declare getEm cursor local for select distinct matchID from scoresByMinute open getEm while (1=1) begin fetch next from getEm into @matchID if (@@fetch_status 0) begin DEALLOCATE getEm break end declare @counter int declare @scoreline varchar(10) set @counter = 1 set @scoreline = '00' while (@counter <= 90) begin select @scoreline = ISNULL(scoreline,@scoreline) from scores where minute = @counter insert into filledScoreLines(matchID, minute, scoreline) select @matchID as matchID, @counter as min, @scoreline as scoreline set @counter = @counter + 1 end end