Pivot values to columns 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
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
;