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.