2

I have a table in database that looks like:

enter image description here

I am trying to create query which gives me result like:

enter image description here

And when I was searching trough the forum I did found information about converting rows to columns using aggregate function and/or using predefined statement. From example what I did found I did try following queries however they doesn't work I don't understand how they work so I simply copy the query. I did try to use my data in it:

select fk_playerID as name, roundID as roundNo, score
from(
roundID,
CASE WHEN roundID = 1 THEN score END AS 1,
CASE WHEN roundID = 2 THEN score END AS 2,
CASE WHEN roundID = 3 THEN score END AS 3,
from cup
) cup group by fk_playerID

and the second one:

SELECT fk_playerID as name, roundID as roundNo, score
MAX(CASE WHEN'roundID' = 1, THEN score end) roundNo1,
MAX(CASE WHEN'roundID' = 2, THEN score end) roundNo2,
MAX(CASE WHEN'roundID' = 3, THEN score end) roundNo3
FROM cup
ORDER BY fk_playerID

And finally my question is how my query must look like and also I need a little explanation how it works.

Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75

1 Answers1

1

The second one is pretty close:

SELECT c.fk_playerID,
       p.Name
       MAX(CASE WHEN c.roundID = 1 THEN c.score END) AS roundNo1,
       MAX(CASE WHEN c.roundID = 2 THEN c.score END) AS roundNo2,
       MAX(CASE WHEN c.roundID = 3 THEN c.score END) AS roundNo3
FROM cup c
JOIN Player p on c.fk_playerID = p.ID
GROUP BY c.fk_playerID, p.Name

You are grouping by fk_playerID. Lets consider player = 1. This

CASE WHEN roundID = 1 THEN score END

will produce the following set for score: {1, null, null}. Max will return 1.

CASE WHEN roundID = 2 THEN score END

will produce the following set for score: {null, 1, null}. Max will return 1.

CASE WHEN roundID = 3 THEN score END

will produce the following set for score: {null, null, 1}. Max will return 1.

The same for player = 19.

Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75
  • Thank you! can you give me a little explanation what this statement actually do so I will understand it and I will be able to use it again when I will have similar problem. And if for example I want to join table 'Players' to get the player name i will join table before MAX or after so the query will give same result + player name ?? –  Jul 08 '15 at 13:12
  • Fantastic answer Thank You Giorgi Nakeuri –  Jul 08 '15 at 13:20