MySQL UNPIVOT Guide

In MySQL, there is no built-in UNPIVOT function for reversing data. However, you can achieve similar functionality using the UNION ALL statement. Here is an example:

Assume there is a table containing the following data:

| id | name | score1 | score2 | score3 |
|----|------|--------|--------|--------|
| 1  | Alice| 80     | 85     | 90     |
| 2  | Bob  | 75     | 70     | 80     |
| 3  | Chris| 90     | 95     | 85     |

To reverse the data, you can use the following SQL statement:

SELECT id, name, 'score1' AS score_type, score1 AS score
FROM table_name
UNION ALL
SELECT id, name, 'score2' AS score_type, score2 AS score
FROM table_name
UNION ALL
SELECT id, name, 'score3' AS score_type, score3 AS score
FROM table_name

After executing the SQL statement above, the following results will be obtained:

| id | name | score_type | score |
|----|------|------------|-------|
| 1  | Alice| score1     | 80    |
| 1  | Alice| score2     | 85    |
| 1  | Alice| score3     | 90    |
| 2  | Bob  | score1     | 75    |
| 2  | Bob  | score2     | 70    |
| 2  | Bob  | score3     | 80    |
| 3  | Chris| score1     | 90    |
| 3  | Chris| score2     | 95    |
| 3  | Chris| score3     | 85    |

This achieves the operation of converting columns in the original table into rows.