0

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
tom bannister
  • 81
  • 1
  • 10
  • 1
    It is known as pivot or crosstab. Depending on your database you might have a function, extension handling it for you. ie: with postgresql you can use tablefunc extension. – Cetin Basoz Mar 22 '23 at 12:37
  • 1
    to my understanding you're converting the rows to columns? If so, this might help [efficiently convert rows to columns in sql server](https://stackoverflow.com/a/15745076/13927534) – Miss Skooter Mar 22 '23 at 12:38

1 Answers1

1

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;
Cetin Basoz
  • 22,495
  • 3
  • 31
  • 39