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.
– M.R., Commented Apr 28, 2011 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
– Andriy M, Commented Apr 28, 2011 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.
– M.R., Commented Apr 28, 2011 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
– pssguy, Commented Apr 28, 2011 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. 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. — Andriy M
– Andriy M, Commented Apr 29, 2011 at 7:45

Andriy M · Accepted Answer · 2011-04-28 17:21:59Z

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, 2011 at 17:21

answered Apr 28, 2011 at 16:53

Andriy M

78k18 gold badges100 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

M.R. Over a year ago

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?

Andriy M Over a year ago

@M.R.: This solution uses CTEs, or Common Table Expressions. 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.

M.R. Over a year ago

cool - will read up. +1 for introducing me to something new :)

M.R. · Accepted Answer · 2011-04-28 17:02:46Z

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

Collectives™ on Stack Overflow

Populating one SQL Server table with values from another

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related