How to Change Table Form to Convert Rows To Columns in MySQL

MySQL is one of the most popular relational database management systems in the world. MySQL allows us to store, manage, and analyze data in tabular form. A table consists of rows and columns that represent entities and data attributes. However, sometimes we need to change the shape of the table to convert rows to columns or vice versa. This is referred to as table transposition.

Transposition tables can be useful for several reasons, such as:

• Simplify data visualization and understanding
• Optimize query and index performance
• Tailor data structures to business or application needs
• Perform aggregation or calculation operations on data

However, MySQL does not have a built-in function to transpose tables directly. Therefore, we need to use certain techniques or tricks to achieve this goal. In this article, we will discuss several ways to change the shape of a table to convert rows to columns in MySQL, along with examples and explanations.

Method 1: Using the Aggregation Function and the GROUP BY Clause

One way to change the form of a table to convert rows to columns in MySQL is to use the aggregation function and the GROUP BY clause. An aggregation function is a function that takes a set of values as input and returns a single value as output. Some commonly used aggregation functions in MySQL are:

• SUM(): Calculates the total number of a set of values
• AVG(): Calculates the average of a set of values
• MIN(): Returns the smallest value of a set of values
• MAX(): Returns the largest value of a set of values
• COUNT(): Counts the number of values present in a set of values

A GROUP BY clause is a clause used to group rows that have the same value in one or more columns. The GROUP BY clause is typically used in conjunction with an aggregation function to calculate aggregate values for each group.

To change the table form to convert rows to columns by using the aggregation function and the GROUP BY clause, we need to do the following steps:

• Specify the column that will be the new row in the results table. This column is usually a column that has different values and not too many. For example, year, month, category, type, etc. columns.
• Specify the column that will be the new column in the results table. This column is usually a column that has the same or limited value for each row. For example, name, product, brand, etc. columns.
• Specify the aggregation function that will be used to calculate the value for each new row and column combination. This aggregation function depends on the type of data and the purpose of our analysis. For example, SUM(), AVG(), MIN(), MAX(), etc.
• Create an SQL query that uses the aggregation function and the GROUP BY clause to return the desired results table.

Let’s look at an example to understand this way better. Suppose we have the following table that stores product sales data for each month and category:

This table has three columns: month, category, and sales. We want to change the shape of this table to convert rows to columns, so we can see the total sales for each category and month in one table. The result table we want is as follows:

To achieve this goal, we can use way 1 with the following steps:

• Specify the column that will be the new row in the results table. In this case, the category column will be a new row, because it has different values and not too many (A, B, C).
• Specify the column that will be the new column in the results table. In this case, the month column will be the new column, because it has the same or limited value for each row (Jan, Feb, Mar).
• Specify the aggregation function that will be used to calculate the value for each new row and column combination. In this case, we’ll use the SUM() function to calculate the total sales for each category and month.
• Create an SQL query that uses the aggregation function and the GROUP BY clause to return the desired results table. The SQL queries that we can use are as follows:

SELECT
category,
SUM(CASE WHEN month = 'Jan' THEN sales ELSE 0 END) AS Jan,
SUM(CASE WHEN month = 'Feb' THEN sales ELSE 0 END) AS Feb,
SUM(CASE WHEN month = 'Mar' THEN sales ELSE 0 END) AS Mar
FROM
sales_table
GROUP BY
category;

This query does the following:

• Select the category column from the table sales_table
• Use the SUM() and CASE functions to calculate total sales for each category and month. CASE is a conditional statement that returns a value based on a given condition. In this query, we use CASE to check the monthly value and return the sales value if appropriate, or 0 if not. For example, SUM(CASE WHEN month = ‘Jan’ THEN sales ELSE 0 END) will return total sales for January for each category, or 0 if there are no sales for that month.
• Give an alias name to each new column by using AS. Alias is an alternative name that we can give to a column or table to make it easier to write and read a query. In this query, we give an alias name to each new column with a corresponding month name, such as Jan, Feb, and Mar.
• Use the GROUP BY clause to group rows by the category column. The GROUP BY clause creates one row for each unique value in the category column and calculates an aggregate value for every other column.

This query will generate the results table we want, as shown above.

Way 2: Using the Pivot Function

Another way to change the shape of a table to convert rows to columns in MySQL is to use the pivot function. The pivot function is a function that allows us to change the shape of a table by rotating data from rows into columns or vice versa. The pivot function can be used to transpose tables easily and quickly.

However, MySQL doesn’t have a built-in pivot function that we can use directly. Therefore, we need to use certain extensions or plugins to add pivot functions to MySQL. One of the popular and frequently used extensions is Flexviews. Flexviews is an extension that provides pivot, unpivot, and incremental refresh functions for MySQL. Flexviews can be downloaded and installed from its official website: https://github.com/greenlion/swanhart-tools/tree/master/flexviews

To change the shape of a table to convert rows to columns by using the pivot function, we need to do as following steps:

• Install and enable the Flexviews extension in MySQL. Follow the installation instructions given on its official website.
• Create a temporary table that contains the data we want to shape. Temporary tables are tables that only exist during a MySQL session and are automatically deleted when the session ends. Temporary tables can be created by using the CREATE TEMPORARY TABLE command. For example, we can create a temporary table with the name sales_temp containing data from the sales_table table with the following command:

CREATE TEMPORARY TABLE sales_temp AS
SELECT * FROM sales_table;

• Create a view that contains the definition of the pivot function we want to use. A view is a database object that stores the results of an SQL query as a virtual table. Views can be created by using the CREATE VIEW command. For example, we can create a view with the name pivot_view that contains the pivot function definition to change the shape of the sales_temp table with the following command:

CREATE VIEW pivot_view AS
SELECT
FV$Pivot(
'sales_temp', -- the name of the temporary table that contains the data
'category', -- the name of the column that will be the new row
'month', -- the name of the column that will become the new column
'sales', -- the name of the column that contains the value to be calculated
'SUM', -- the name of the aggregation function to use
NULL -- additional column names to include in the results table (optional)
) AS pivot_result; -- alias name for the pivot function result

• Run an SQL query to call the pivot function of the view that has been created. The SQL queries that we can use are as follows:

SELECT * FROM pivot_view;

This query will generate the results table we want, as shown above.

Way 3: Using the Unpivot and Pivot Functions

The third way to change the shape of a table to convert rows to columns in MySQL is to use the unpivot and pivot functions. The unpivot function is the opposite function of the pivot function, which changes the shape of the table by rotating data from columns into rows. The unpivot function can be used to transpose tables more flexibly and dynamically.

However, MySQL also doesn’t have a built-in unpivot function that we can use directly. Therefore, we also need to use certain extensions or plugins to add unpivot functionality to MySQL. One of the popular and frequently used extensions is the Flexviews we discussed earlier. Flexviews provides an unpivot function in addition to pivot and incremental refresh functions for MySQL.

To change the table shape to convert rows to columns by using the unpivot and pivot functions, we need to do as following steps:

• Install and enable the Flexviews extension in MySQL if it hasn’t been done before. Follow the installation instructions given on its official website
• Create a temporary table that contains the data we want to shape, if it didn’t already exist before. Temporary tables can be created by using the CREATE TEMPORARY TABLE command. For example, we can create a temporary table with the name sales_temp containing data from the sales_table table with the following command:

CREATE TEMPORARY TABLE sales_temp AS
SELECT * FROM sales_table;

• Create a view that contains the definition of the unpivot function we want to use. A view is a database object that stores the results of an SQL query as a virtual table. Views can be created by using the CREATE VIEW command. For example, we can create a view with the name unpivot_view containing an unpivot function definition to change the shape of a sales_temp table with the following command:

CREATE VIEW unpivot_view AS
SELECT
FV$Unpivot(
'sales_temp', -- the name of the temporary table that contains the data
'category', -- the name of the column that will remain in the row
'month', -- the name of the new column that will store the old column name
'sales', -- the name of the new column that will store the old column values
'month, sales' -- the name of the old column to be converted to a row
) AS unpivot_result; -- alias name for unpivot function results

• Create another view that contains the definition of the pivot function we want to use. Views can be created by using the CREATE VIEW command. For example, we can create a view with the name pivot_view containing the pivot function definition to change the shape of the unpivot_view table with the following command:

CREATE VIEW pivot_view AS
SELECT
FV$Pivot(
'unpivot_view', -- the display name that contains the data
'category', -- the name of the column that will be the new row
'month', -- the name of the column that will become the new column
'sales', -- the name of the column that contains the value to be calculated
'SUM', -- the name of the aggregation function to use
NULL -- additional column names to include in the results table (optional)
) AS pivot_result; -- alias name for the pivot function result

• Run an SQL query to call the pivot function of the view that has been created. The SQL queries that we can use are as follows:

SELECT * FROM pivot_view;

This query will generate the results table we want, as shown above.

Conclusion

In this article, we have discussed several ways to change the form of a table to convert rows to columns in MySQL, along with examples and explanations. Some of the ways we can use are:

• Using aggregation functions and GROUP BY clauses
• Use the pivot function
• Use the unpivot and pivot functions

Each method has its advantages and disadvantages, depending on the data structure, the purpose of analysis, and our preferences. We can choose the way that best suits our needs and conditions.

Hopefully, this article is useful and can help you in transposing tables in MySQL. Thank you for reading this article to the end.