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

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.

Other Interesting Articles

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.

Latest Articles

Related Articles