How to Create Dynamic Pivot Tables in MySQL

Last updated on May 17th, 2020 at 06:18 am

Pivot tables make it easy to analyze large volumes of data by organizing information into a smaller, manageable data set. However, there is no built-in function to achieve pivot in MySQL. We need to write a SQL query to convert row to column in MySQL, and then create a pivot report in MySQL. So let’s look at how to create dynamic pivot tables in MySQL.

Table of Contents

How to Create Dynamic Pivot Tables in MySQL

If you want to create a pivot table in MySQL, you would typically use IF/CASE statements. However, this approach works only when you already know all the columns you need to create in a pivot table.

How do you create dynamic pivot tables in MySQL, when you don’t know the columns to be created, or if you expect them to change over time? In such cases, we use the GROUP_CONCAT function.

Let’s say you have the following table

CREATE TABLE Meeting
(
    ID INT,
    Meeting_id INT,
    field_key VARCHAR(100),
    field_value VARCHAR(100)
);

INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (1, 1,'first_name' , 'Alec');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (2, 1,'last_name' , 'Jones');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (3, 1,'occupation' , 'engineer');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (4,2,'first_name' , 'John');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (5,2,'last_name' , 'Doe');
INSERT INTO Meeting(ID,Meeting_id,field_key,field_value)
VALUES (6,2,'occupation' , 'engineer');

+------+------------+------------+-------------+
| ID   | Meeting_id | field_key  | field_value |
+------+------------+------------+-------------+
|    1 |          1 | first_name | Alec        |
|    2 |          1 | last_name  | Jones       |
|    3 |          1 | occupation | engineer    |
|    4 |          2 | first_name | John        |
|    5 |          2 | last_name  | Doe         |
|    6 |          2 | occupation | engineer    |
+------+------------+------------+-------------+

Let’s say you want to create dynamic pivot table, such that a new column is created for each unique value in field_key column, that is (first_name, last_name, occupation)

+------------+-------------+-------------+-------------+
| Meeting_id | first_name  |  last_name  |  occupation |
+------------+-------------+-------------+-------------+
|          1 |       Alec  | Jones       | engineer    |
|          2 |       John  | Doe         | engineer    |
+------------+-------------+-------------+-------------+

Create Dynamic Pivot Tables in MySQL

If you already know which columns to create in pivot table, you can use a CASE statement to create a pivot table. However, to create dynamic pivot tables in MySQL, we use GROUP_CONCAT function to dynamically transpose rows to columns, as shown below.

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when field_key = ''',
      field_key,
      ''' then field_value end) ',
      field_key
    )
  ) INTO @sql 
FROM
  Meeting;
SET @sql = CONCAT('SELECT Meeting_id, ', @sql, ' 
                  FROM Meeting 
                   GROUP BY Meeting_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

GROUP_CONCAT allows you to concatenate values from multiple rows into a single string. In the above query, we use GROUP_CONCAT to dynamically create CASE statements, based on unique values in the field_key column and store that string in @sql variable. It is then used to create our select query.

+------------+------------+-----------+------------+
| Meeting_id | first_name | last_name | occupation |
+------------+------------+-----------+------------+
|          1 | Alec       | Jones     | engineer   |
|          2 | John       | Doe       | engineer   |
+------------+------------+-----------+------------+

This way you can automate pivot table queries in MySQL.

You can customize the above query as per your requirements by adding WHERE clause or JOINS.

If you want to pivot only select row values as columns, you can add WHERE clause in your 1st select GROUP_CONCAT statement, as shown in bold below

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when field_key = ''',
      field_key,
      ''' then field_value end) ',
      field_key
    )
  ) INTO @sql
FROM
  Meeting
WHERE <condition>;

Also read: How to Unpivot Table in MySQL

If you want to filter rows in your final pivot table, you can add the WHERE clause in your SET statement as shown in bold below.

SET @sql = CONCAT('SELECT Meeting_id, ', @sql, ' 
                  FROM Meeting WHERE <condition>
                   GROUP BY Meeting_id');

Similarly, you can also apply JOINS in your SQL query while you create dynamic pivot tables in MySQL.

Once you create dynamic pivot tables in MySQL, you can use a reporting tool to plot it in a table. Here’s an example of pivot table created using Ubiq.