How to Return Pivot Table Output in MySQL?

Pivot in SQL is helpful when you need to convert row-based data into columns to make the data easier to analyze. MySQL does not have built-in functions for PIVOT like SQL Server, but it can be used in MySQL by using CASE statements with aggregate functions or dynamic SQL with GROUP_CONCAT(). In this blog, let us explore the different methods by which we can return the Pivot Table as an output in MySQL

Table of Contents:

Let’s create a table and insert some values into it that can be used as an example for the following methods 

CREATE TABLE restaurant_ratings (
    restaurant_id INT AUTO_INCREMENT PRIMARY KEY,
    restaurant_name VARCHAR(100),
    city VARCHAR(50),
    rating INT
);
INSERT INTO restaurant_ratings (restaurant_name, city, rating) VALUES
('Gourmet Delight', 'New York', 5),
('Gourmet Delight', 'Los Angeles', 4),
('Gourmet Delight', 'Chicago', 3),
('Tasty Treats', 'New York', 4),
('Tasty Treats', 'Los Angeles', 5),
('Tasty Treats', 'Chicago', 3),
('Food Paradise', 'New York', 3),
('Food Paradise', 'Los Angeles', 4),
('Food Paradise', 'Chicago', 5);
SET @sql = NULL;
SELECT * FROM restaurant_ratings;

This is how the table looks after creating and inserting the data.

Master the PIVOT in MySQL – Unlock Advanced SQL Skills Today!

Enroll now and transform your future!

Explore Program

Methods to Return Pivot Table Output in MySQL

There are several methods to return pivot table output in MySQL, like using CASE with aggregate, dynamic PIVOT, cross-tabulation (organizing data in table) with joins, etc. 

Method 1: Using Dynamic Columns with GROUP_CONCAT in MySQL

Dynamic columns with GROUP_CONCAT() in MySQL are used in dynamically generated columns. It can be used when the names of the columns are known in advance. It is very efficient when we use tables that update dynamically. 

Example:

SET @sql = NULL;
SELECT 
    GROUP_CONCAT(DISTINCT 
        CONCAT(
            'MAX(CASE WHEN city = ''', city, ''' THEN rating ELSE NULL END) AS `', city, '`'
        )
    ) INTO @sql
FROM restaurant_ratings;
SET @sql = CONCAT('SELECT restaurant_name, ', @sql, ' FROM restaurant_ratings GROUP BY restaurant_name');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Output:

Explanation: The dynamic columns with GROUP_CONCAT() converted the output strings into columns for easier access. 

Method 2: Using CASE with aggregate functions in MySQL

The CASE inside an aggregate function like MAX(), SUM(), and GROUP BY returns the pivot table output in MySQL.

1. Using CASE with GROUP BY in MySQL

The CASE with GROUP BY in MySQL is the simplest way to pivot data when the column values are known. 

Example:

SELECT 
    city,
    CASE 
        WHEN AVG(rating) >= 4.5 THEN 'Excellent'
        WHEN AVG(rating) BETWEEN 3.5 AND 4.4 THEN 'Good'
        ELSE 'Average'
    END AS Rating_Category
FROM restaurant_ratings
GROUP BY city;

Output:

Explanation: Here, the CASE with the GROUP BY function gets the rating by conditioning the range of ratings and converting them into columns for easier access. 

2. Using SUM with CASE in MySQL

The SUM with CASE makes sure that aggregated data is distributed evenly. 

Example:

SELECT 
    restaurant_name,
    SUM(CASE WHEN city = 'New York' THEN rating ELSE 0 END) AS Total_New_York,
    SUM(CASE WHEN city = 'Los Angeles' THEN rating ELSE 0 END) AS Total_Los_Angeles,
    SUM(CASE WHEN city = 'Chicago' THEN rating ELSE 0 END) AS Total_Chicago
FROM restaurant_ratings
GROUP BY restaurant_name;

Output:

Explanation: You can find the restaurant in each area by using SUM(), which combines the count of the restaurant. GROUP BY restaurant_name ensures that all SUM results are combined and pivoted.

3. Using MAX() with CASE in MySQL

The MAX() function gets the data. It does not require dynamic SQL. 

Example:

SELECT 
    restaurant_name,
    MAX(CASE WHEN city = 'New York' THEN rating ELSE NULL END) AS New_York,
    MAX(CASE WHEN city = 'Los Angeles' THEN rating ELSE NULL END) AS Los_Angeles,
    MAX(CASE WHEN city = 'Chicago' THEN rating ELSE NULL END) AS Chicago
FROM restaurant_ratings
GROUP BY restaurant_name;

Output:

Explanation: Here, the MAX() function acquires the data of ratings of each restaurant per city. 

Method 3: Cross Tabulation Methods with Joins function in MySQL

The Cross Tabulation Method is an alternative method to return output like PIVOT. It will not use aggregate functions like GROUP BY or SUM(). This method uses joins like self-joins and cross-joins that convert rows into column format. 

1. Using Self-JOIN 

You can use the joins with the table itself and can filter it based on the conditions specified. The self-join extracts the specific data. It can work efficiently when the table is smaller. 

Example:

SELECT r1.restaurant_name,
       r1.rating AS New_York,
       r2.rating AS Los_Angeles,
       r3.rating AS Chicago
FROM restaurant_ratings r1
LEFT JOIN restaurant_ratings r2 
    ON r1.restaurant_name = r2.restaurant_name 
    AND r2.city = 'Los Angeles'
LEFT JOIN restaurant_ratings r3 
    ON r1.restaurant_name = r3.restaurant_name 
    AND r3.city = 'Chicago'
WHERE r1.city = 'New York';

Output:

Explanation: Here, the self-join assigns the rows with r1, r2, and r3 for restaurants in New York, Los Angeles, and Chicago, respectively and extracts ratings from them. The left join makes sure that the NULL value is not eliminated. 

2. Using Cross Join with Aggregation

The Cross join produces a product that is cartesian and helps in structured transformation, then uses an aggregate function like MAX() to pivot the values. 

Example:

SELECT 
    r.restaurant_name,
    MAX(CASE WHEN c.city = 'New York' THEN r.rating ELSE NULL END) AS New_York,
    MAX(CASE WHEN c.city = 'Los Angeles' THEN r.rating ELSE NULL END) AS Los_Angeles,
    MAX(CASE WHEN c.city = 'Chicago' THEN r.rating ELSE NULL END) AS Chicago
FROM restaurant_ratings r
CROSS JOIN (SELECT DISTINCT city FROM restaurant_ratings) c
GROUP BY r.restaurant_name;

Output:

Explanation: Here, the cross-join creates a matrix structure temporarily with cities and restaurants, then CASE with MAX() combines the data into columns, and then GROUP BY displays the table. 

Alternative Dynamic SQL for Unknown Column Values

You can use the dynamic tables where the value of columns updates over time. But in this we cannot use the CASE statement, so instead we can use the GROUP_CONCAT() and PREPARE statements.

Example:

CREATE TABLE sales (
    product VARCHAR(100),
    year INT,
    sales DECIMAL(10,2)
);
INSERT INTO sales (product, year, sales) VALUES
('Laptop', 2022, 50000),
('Laptop', 2023, 60000),
('Phone', 2022, 30000),
('Phone', 2023, 40000),
('Tablet', 2022, 20000),
('Tablet', 2023, 25000);
SET @sql = NULL;
SELECT 
    GROUP_CONCAT(DISTINCT 
        CONCAT('SUM(CASE WHEN year = ', year, ' THEN sales ELSE 0 END) AS `', year, ' Sales`')
    ) INTO @sql
FROM sales;
SET @sql = CONCAT('SELECT product, ', @sql, ' FROM sales GROUP BY product');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Output:

Explanation: Here, the GROUP_CONCAT fetches the dynamic unique year values and creates a pivot column. The PREPARE statement compiles and executes the query. 

Performance Comparison

Real World Examples

Case 1: The meteorological department collects daily temperature data from different stations. The PIVOT will collect all the data and display it in columnar format. 

Example:

CREATE TABLE weather_data (
    station_id INT AUTO_INCREMENT PRIMARY KEY,
    station_name VARCHAR(100),
    date DATE,
    temperature DECIMAL(5,2)
);
INSERT INTO weather_data (station_name, date, temperature) VALUES
('Station A', '2024-03-01', 15.0),
('Station A', '2024-03-02', 18.0),
('Station B', '2024-03-01', 20.0),
('Station B', '2024-03-03', 22.0);
SELECT 
    station_name,
    MAX(CASE WHEN date = '2024-03-01' THEN temperature ELSE NULL END) AS "March 1",
    MAX(CASE WHEN date = '2024-03-02' THEN temperature ELSE NULL END) AS "March 2",
    MAX(CASE WHEN date = '2024-03-03' THEN temperature ELSE NULL END) AS "March 3"
FROM weather_data
GROUP BY station_name;

Output:

Explanation: Here, the pivot shows the temperature changes from different stations for different days.

Case 2: Movie streaming analytics

In the movie streaming platform, we need to fetch the records of monthly watch time per genre for users. We need to pivot the data to evaluate the monthly genre-wise trend. 

Example:

CREATE TABLE watch_time (
    user_id INT,
    genre VARCHAR(50),
    month VARCHAR(7),  
    hours_watched INT
);
INSERT INTO watch_time (user_id, genre, month, hours_watched) VALUES
(101, 'Action', '2024-02', 10),
(101, 'Comedy', '2024-02', 5),
(102, 'Action', '2024-02', 7),
(101, 'Action', '2024-03', 12),
(102, 'Comedy', '2024-03', 8);
INSERT INTO watch_time (user_id, genre, month, hours_watched) VALUES
(102, 'Horror', '2024-03', 23);
SET @sql = NULL;
SELECT 
    GROUP_CONCAT(DISTINCT 
        CONCAT('SUM(CASE WHEN month = ''', month, ''' THEN hours_watched ELSE 0 END) AS `', month, '`')
    ) INTO @sql
FROM watch_time;
SET @sql = CONCAT('SELECT genre, ', @sql, ' FROM watch_time GROUP BY genre');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Output:

Explanation: Using dynamic SQL with the PREPARE statement, we fetched the data of monthly records of genres watched by the user. 

Start Your SQL Adventure!

Start Your SQL Journey for Free Today

Explore Program

Conclusion

To convert rows into columns, you can use PIVOT in MySQL, which makes the data more readable in a tabular format that will make you analyze the data more easily. Since MySQL doesn’t have a built-in PIVOT function like SQL Server, it uses approaches like CASE with aggregate functions, dynamic SQL with GROUP_BY CONCAT(), PREPARE statements for dynamic column values, and the cross-tabulation method with self-join and cross-join. Based on the dataset, you can choose the best technique to get the desired output. 

You can learn more about SQL in the SQL Course and also explore SQL Interview Questions prepared by industry experts.

How to Return Pivot Table Output in MySQL – FAQs