CODEWITHSUNDEEP
sql-server-2005t-sql
pssguy
pssguy

Reputation: 3505

Populating one SQL Server table with values from another

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?

Upvotes: 4

Views: 442

Answers (2)

Andriy M
Andriy M

Reputation: 77677

;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

Upvotes: 2

M.R.
M.R.

Reputation: 4827

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

Upvotes: 2

Related Questions