Search code examples
sql-serverdatatablessms

Turning a SQL Server table with duplicate IDs into a new table with multiple columns

I have data from a table (Table1), my aim is to have a new table with a unique ID (in the case below it would be 906469), then in the next column I would need the odds (e.g. CS 0:0 has odds of 27). The odds of 27 would need to go into a column called CS 0:0. This needs to repeat for each score, is this possible? (Apologies, I struggled to put tables of data in here, I had to use images.)

From this:

Table1:

table1

To this - new table:

enter image description here

Solution

  • It looks like you want to do this for a GPA scores table where those scores are 0.0, 0.1, ... 4.0 then you might simply use "case when" construct to build it and it would work in almost any database and any version. ie:

    select fixtureid,
   max(case when correctscore = 'CS 0.0' then odds end) as "0.0.Odds",
   max(case when correctscore = 'CS 0.1' then odds end) as "0.1.Odds",
   max(case when correctscore = 'CS 0.2' then odds end) as "0.2.Odds",
-- ...
   max(case when correctscore = 'CS 4.0' then odds end) as "4.0.Odds"
into newTable
from myTable
group by fixtureid;