Populating one SQL Server table with values from another

Question

I have a table scoresByMinute which shows the scoreline whenever a goal is scored at soccer

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

I wish to create a new table which shows the scoreline for each minute of each 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 should I go about this?

what is scoreline? as in, when you have '10' does it mean 1-0 (the score) ? — M.R., Apr 28 '11 at 16:50
@M.R.: Good question! I didn't pay attention to it until you asked. And I think you've guessed correctly, that's very much like the score. (And by the way, the OP doesn't seem to allow for a score to be greater than 9.) — Andriy M, Apr 28 '11 at 17:24
cool - the solution I posted will work either way.... if you decide to split it up into separate fields, that is.. — M.R., Apr 28 '11 at 17:27
Thanks guys for the postings and comments. Yes the scoreline is as you suggested the score at any point in time so a character field created from to tinyints, homescore and awayscore. Sorry I should have mentioned that. It does not cater for teams scoring more than 9 in a game which has not happened in 20 years in the league I am looking at and does not matter for my purpose anyways . Andriy, thanks very much for this it works great with my real data. I will need to read up more on the method but can you just explain what the line INNER JOIN master..spt_values v ON v.type = 'P' is about — pssguy, Apr 28 '11 at 19:04
Oh, I must apologise too then, I should at least have briefed you about that table. You can start reading from [this question](http://stackoverflow.com/questions/4273723/what-is-the-purpose-of-system-table-table-master-spt-values-and-what-are-the-mea). In short, this is a system table that has existed for a long time and is used for internal purposes. The `v.type = 'P'` filter gives a subset of the table with a sequence of unique `number` values from 0 till 2047. So, I'm just using the table (specifically the subset) as a *[tally table](http://www.sqlservercentral.com/articles/T-SQL/62867/)*. — Andriy M, Apr 29 '11 at 07:45

Andriy M · Accepted Answer · 2011-04-28T17:21:59.997

2

;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

edited Apr 28 '11 at 17:21

answered Apr 28 '11 at 16:53

Andriy M

76,112
17
94
154

is this a chaining of table statements? I'm not too familiar with doing it this way, but certainly looks much slicker than mine. Can you share some details where I can read up on this? – M.R. Apr 28 '11 at 17:09
1

@M.R.: This solution uses CTEs, or [Common Table Expressions](http://msdn.microsoft.com/en-us/library/ms175972.aspx). They are in essence derived tables, like subselects, and are valid within the scope of the single statement they are preceding. (In this case – `SELECT *...` at the end of the script, but it may also be `INSERT`, `UPDATE`, or `DELETE`.) The remarkable thing about them is, unlike subselects, they can be referenced in the statement multiple times, by their names (i.e. you can derive multiple result sets from them). They weren't around in SQL Server until the 2005 version. – Andriy M Apr 28 '11 at 17:19
cool - will read up. +1 for introducing me to something new :) – M.R. Apr 28 '11 at 17:36

score 2 · Answer 2 · answered Apr 28 '11 at 17:02

Create a new table with the structure you want, and then run this 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

To do it for multiple matches, just loop over all the match IDs you have - like so:



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

Thanks for your solution. By the time I had got back to the page you had already commented favourably on the alternative approach so I concentrated on that. Hope you find CTE's useful in future — pssguy, Apr 28 '11 at 19:08

Populating one SQL Server table with values from another

2 Answers2