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.

Improve this question

1 Answer 1

Reset to default
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.

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

2 Comments

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 ??
Fantastic answer Thank You Giorgi Nakeuri

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.