Can MySQL Transform Long-Format Data to Wide-Format Without External Scripts?

Reshaping Data from Long to Wide Format in MySQL

In MySQL, data is often stored in a long or tall format, where multiple attributes are stored in separate rows for the same entity. However, for analysis and reporting purposes, it can be more convenient to have the data in a wide format, where each row represents an entity and each column represents an attribute.

Can MySQL be used to convert long format data to wide format without external scripting? Yes, it's possible using the powerful pivot table capabilities of MySQL.

Creating the Wide Table

1. Fetch Key List: Retrieve all distinct keys present in the long format table using a query like:

   SELECT DISTINCT key FROM table;

2. Create the Wide Table: Create a new table with the required columns. The column names should correspond to the keys. For example, if you have the keys President and Currency, your table should contain columns named President and Currency.

Filling in the Table Values

Use a cross-tab query to fill in the values in the wide table. The following query demonstrates this:

SELECT country,
       MAX(IF(key='President', value, NULL)) AS President,
       MAX(IF(key='Currency', value, NULL)) AS Currency,
       ...  -- Add other key-value pairs as needed

FROM table
GROUP BY country;

This query checks each row in the long table and returns only the latest value for each key-value pair for that country. The MAX function with the IF condition ensures that the most recent value is retained.

For example, the input data provided in the question would be converted to the following wide format:

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
US	Obama	Dollar
China	Hu	Yuan

Conclusion

Using MySQL pivot tables, it is straightforward to reshape data from long to wide format, making it more suitable for analysis and reporting. This method is efficient and can be applied to tables with any number of attributes.

The above is the detailed content of Can MySQL Transform Long-Format Data to Wide-Format Without External Scripts?. For more information, please follow other related articles on the PHP Chinese website!