How Can I Reshape Long/Tall Data to Wide Format in MySQL?

Reshaping Data in MySQL: Converting Long/Tall to Wide Format

In data analysis, it is often necessary to reshape data from a long/tall format to a wide format. This transformation converts a table with multiple rows and columns into a table with fewer rows and more columns.

Problem Statement

Consider a MySQL table with data in long/tall format, where each row represents a single observation with a country, key, and value. The goal is to reshape the data into wide format, with one row for each country and columns for each key.

SQL Solution

MySQL provides a built-in feature called cross-tabs or pivot tables to accomplish this transformation. Here's how:

1. Identifying Keys

Use a query to select distinct keys from the original table:

SELECT DISTINCT key FROM table;

The output of this query will provide a list of key elements.

2. Creating the Wide Table

Create a new table with the following structure:

CREATE TABLE wide_table (
  country VARCHAR(255),
  key1 VARCHAR(255),
  ...,
  keyN VARCHAR(255),
  newPkey INT AUTO_INCREMENT PRIMARY KEY
);

Replace key1, ..., keyN with the actual key names obtained in step 1. The newPkey column is an auto-incrementing primary key used to uniquely identify each row in the wide table.

3. Filling Table Values

Use a cross-tab query to populate the wide 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 utilizes conditional aggregation (MAX and IF) to assign values to the key columns for each country. Rows with duplicate keys for the same country are eliminated by using the MAX function.

Example Input and Output

Input:

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

Output:

country	President	Currency	newPkey
US	Obama	Dollar	1
China	Hu	Yuan	2

This example demonstrates the transformation from long/tall to wide format using MySQL cross-tabs.

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