How to Efficiently Reshape Data from Long to Wide Format in MySQL?

Reshaping Data from Long to Wide Format in MySQL

Introduction:

Reshaping data from a long (tall) format to a wide format can be a necessity when manipulating data. MySQL provides features that enable you to perform this operation without relying on external scripting languages.

Creating the Wide Format Table:

1. The first step is to obtain a list of all distinct keys present in the long format table:

   SELECT DISTINCT key FROM table;

2. Use the obtained keys to create a new table with the desired wide format structure:

   CREATE TABLE wide_table (
       country VARCHAR(255),
       key1 VARCHAR(255),
       key2 VARCHAR(255),
       ...,
       keyN VARCHAR(255)
   );

Filling in the Table Values:

3. Finally, insert the data into the wide format table using a cross-tabs query:

   INSERT INTO wide_table (
       country,
       key1,
       key2,
       ...,
       keyN
   )
   SELECT
       country,
       MAX(IF(key = 'key1', value, NULL)) AS key1,
       MAX(IF(key = 'key2', value, NULL)) AS key2,
       ...,
       MAX(IF(key = 'keyN', value, NULL)) AS keyN
   FROM table
   GROUP BY country;

Example:

Consider the long format input table:

country	attrName	attrValue	key
US	President	Obama	2
US	Currency	Dollar	3
China	President	Hu	4
China	Currency	Yuan	5

Using the above steps, we can reshape the data into wide format:

Wide Format Output:

country	President	Currency
US	Obama	Dollar
China	Hu	Yuan

Benefits of Using SQL:

• Efficiency: SQL queries are optimized for database operations, minimizing processing time.
• Declarative: SQL allows you to express the desired transformation in a concise and readable manner.
• Portability: SQL queries can be executed across different MySQL databases, ensuring compatibility without modifications.
• Maintainability: Once the SQL query is developed, it can be easily maintained and altered as needed.

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