How to Reshape MySQL Data from Narrow to Wide Format Using SQL?

Reshaping MySQL Data from Narrow to Wide Format

Introduction

Data often comes in various formats, and有时候, it is necessary to convert it from one format to another. One common conversion is from narrow/tall format to wide format, where multiple columns in the narrow table become multiple rows in the wide table. This article explores how to achieve this conversion in MySQL.

Creating the Wide Table Structure

To create a wide format table, first identify all the unique keys from the narrow table. This can be achieved using the following query:

select distinct key from table;

The result of this query will provide a list of all the unique keys in the narrow table. These keys will become the column names for the wide format table. For instance, if the distinct keys are ['President', 'Currency'], the wide format table will have the following structure:

country, President, Currency

Populating the Wide Table Data

To fill in the values for the wide format table, use a SELECT statement with aggregate functions such as MAX() or GROUP_CONCAT():

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

In this example, the MAX() aggregate function is used to retrieve the maximum value for each key within each country. This query would produce the following result:

country, President, Currency
US, Obama, Dollar
China, Hu, Yuan

Alternative Approach: Cross-Tabs or Pivot Tables

An alternative approach to creating a wide format table is to use cross-tabs or pivot tables. This can be achieved using tools such as MySQL's PIVOT operator, which allows you to specify the columns and aggregate functions you want to use.

Conclusion

Reshaping data from narrow to wide format can be easily achieved in MySQL using SQL aggregate functions or cross-tabs/pivot tables. This conversion is particularly useful when working with data visualization tools such as charts and graphs.

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