How to Reshape Tall Data to Wide Data in MySQL?

Reshaping Data in MySQL: From Tall to Wide

When working with data in a database, it's often necessary to reshape the data from a long, tall format to a wider format. This conversion makes it easier to analyze and visualize the data in a more compact and meaningful way.

Example:

Consider a table with data in a long format:

| country | key | value |
|---|---|---|
| USA | President | Obama |
| USA | Currency | Dollar |
| China | President | Hu |
| China | Currency | Yuan |

In this format, each row represents a single attribute for a country. To convert this data to a wide format, we'll create a new table with columns for each unique key and populate them with the corresponding values for each country:

| country | President | Currency |
|---|---|---|
| USA | Obama | Dollar |
| China | Hu | Yuan |

SQL Conversion:

MySQL provides the ability to reshape data using cross-tabs or pivot tables. Here's an example query that will generate the desired wide-format table:

SELECT country, 
       MAX( IF( key='President', value, NULL ) ) AS President,
       MAX( IF( key='Currency', value, NULL ) ) AS Currency
FROM table
GROUP BY country;

This query uses the MAX() aggregation function to find the maximum value (i.e., the value associated with each unique key) for each country.

Steps:

1. Create the Table: Execute the SELECT query above to create a new table with the desired wide format.
2. Fill in Values: The query will automatically populate the values based on the aggregation.

Conclusion:

Using cross-tabs or pivot tables in MySQL, it's possible to easily reshape data from a long, tall format to a wide format. This is a valuable technique for data analysis and visualization, and it can be easily implemented in MySQL using the provided code.

The above is the detailed content of How to Reshape Tall Data to Wide Data in MySQL?. For more information, please follow other related articles on the PHP Chinese website!