CODEWITHSUNDEEP
mysql
user3396201
user3396201

Reputation:

convert rows to columns in mysql

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.

Upvotes: 2

Views: 96

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

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.

Upvotes: 1

Related Questions