Introduction:

As developers and data analysts, we often come across scenarios where we need to pivot our data from a single-row result into columns. This transformation allows us to present the data in a more organized and readable format, making it easier to analyze and interpret the results. In this blog, we will explore multiple methods to achieve this transformation.

Sample Data:

Before diving into the methods, let's consider a hypothetical scenario where we have a table named "sales_data" that contains the following data:

Our goal is to pivot this single-row result into columns based on the "Category" field, resulting in the following structure:

Method 1: Using MAX() Function with CASE Statement

One of the simplest ways to achieve this pivot transformation is by using the MAX() function in conjunction with the CASE statement. The MAX() function helps us aggregate the data, while the CASE statement acts as a conditional expression to place the values in the appropriate columns.

SELECT
  Year,
  MAX(CASE WHEN Category = 'Category1' THEN Revenue END) AS Category1,
  MAX(CASE WHEN Category = 'Category2' THEN Revenue END) AS Category2,
  MAX(CASE WHEN Category = 'Category3' THEN Revenue END) AS Category3
FROM sales_data
GROUP BY Year;

In this method, we use the SELECT statement to retrieve the data and perform the pivot transformation. Inside the SELECT clause, we use the MAX() function along with the CASE statement for each category. The CASE statement checks if the "Category" matches the desired category ('Category1', 'Category2', etc.), and if so, it returns the "Revenue" value; otherwise, it returns NULL.

The GROUP BY clause groups the result by the "Year" column, ensuring that we get a single-row result for each year.

Output:

Method 2: Utilizing GROUP_CONCAT() Function

Another approach to pivot our single-row result into columns is by using the GROUP_CONCAT() function. This method is particularly useful when dealing with string values and multiple columns with distinct categories.

SELECT
  Year,
  GROUP_CONCAT(CASE WHEN Category = 'Category1' THEN Revenue END) AS Category1,
  GROUP_CONCAT(CASE WHEN Category = 'Category2' THEN Revenue END) AS Category2,
  GROUP_CONCAT(CASE WHEN Category = 'Category3' THEN Revenue END) AS Category3
FROM sales_data
GROUP BY Year;

In this method, we use the GROUP_CONCAT() function within the SELECT statement instead of the MAX() function. The GROUP_CONCAT() function aggregates the values into a comma-separated string. The CASE statement works similarly, placing the "Revenue" value in the appropriate category's column and NULL for other categories.

Output:

Conclusion:

In this blog, we explored various methods to transform a single-row result into columns in MySQL. We learned two primary approaches: using the MAX() function with CASE statements and utilizing the GROUP_CONCAT() function. Both methods achieved the desired pivot transformation, allowing us to organize our data in a more structured and readable format.