Mysql – Pivot values to columns

MySQLpivot

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

Best Answer

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.