Pivot values to columns

Question

I'm trying to create a view that groups score data for each week and displays it for each user but I don't know which commands to use.

Sample data:

// scorecards table
week | user | score |
1 | 1 | 100 |
1 | 2 | 50 |
1 | 3 | 75 |
2 | 1 | 20 |
2 | 2 | 30 |
2 | 3 | 40 |

Desired output:

week | user1 | user2 | user3
1 | 100 | 50 | 75
2 | 20 | 30 | 40

• Akina: If userlist is fixed list, you may use conditional grouping to pivot data. If not, you need to create stored procedure which uses prepared statement to obtain the data in the form you need.

Answer 1

MySQL pivot can be approximated through the use of aggregates, as in:

SELECT s.score_week
 , User1 = sum(s.user1)
 , User2 = sum(s.user2)
 , User3 = sum(s.user3)
FROM (
 SELECT s.score_week
 , CASE WHEN s.score_user = 1 THEN SUM(s.score) ELSE 0 END AS User1
 , CASE WHEN s.score_user = 2 THEN SUM(s.score) ELSE 0 END AS User2
 , CASE WHEN s.score_user = 3 THEN SUM(s.score) ELSE 0 END AS User3
 FROM scorecards s
 GROUP BY s.score_week
 , s.score_user
 ) s
GROUP BY s.score_week;

See the fiddle here for more details.

The output looks like:

╔════════════╦═══════╦═══════╦═══════╗
║ score_week ║ User1 ║ User2 ║ User3 ║
╠════════════╬═══════╬═══════╬═══════╣
║ 1 ║ 100 ║ 50 ║ 75 ║
║ 2 ║ 20 ║ 30 ║ 40 ║
╚════════════╩═══════╩═══════╩═══════╝

The above example is hard-coded, and assumes the list of users doesn't change. If the list of users is dynamic, and more users get added weekly, you'll need to use dynamic SQL to build the query to include columns for each user at run-time.

Answer 2

Since MySQL doesn't allow FULL OUTER JOIN this is a bit trickier, but doable taking the week as main key for the left joins.

select distinct s.week, user1, user2, user3 from scorecards s left outer join 
(select week, score as user1 from scorecards where user = 1) a
on s.week = a.week left join
(select week, score as user2 from scorecards where user = 2) b
on s.week = b.week left join
(select week, score as user3 from scorecards where user = 3) c
on s.week = c.week
;

• ypercubeᵀᴹ: MySQL has OUTER joins (LEFT and RIGHT), like the ones you used! It only lacks FULL outer joins. (I guess you meant that it doesn't have FULL OUTER JOIN.)
• LironCareto: Good point, I meant full outer join, which is what I would use in this case in other DBMS. I'll edit the answer, thanks for point it out.
• Akina: In MySQL it's enough to use something like SELECT `week`, MAX(CASE WHEN `user`=1 THEN score END) user1, ... FROM scorecards GROUP BY `week` .
• LironCareto: I think your solution is only partially right, @Akina because if you have more than one score per user and week you would be missing it. And I didn't know if that was the case.
• Akina: if you have more than one score per user and week you would be missing it The task has no sense if (week,user) is no UNIQUE - otherwise the author would say what to do in this case (sum, concat, min/max, etc.). Your solution will give strange output (multiple rows per user and week pair) in that case too...