How to Automate Pivot Table Queries in MySQL

Sreeram Sreenivasan

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

Pivot Tables make it easy to analyze your data and get useful trends. However, you might need to automate pivot table queries to use them frequently. Since MySQL doesn’t have a function for creating pivot tables, you need to write SQL query to automate pivot tables in MySQL. So let’s look at how to automate pivot table queries in MySQL

 

How to Automate Pivot Table Queries in MySQL

Here are the steps to automate pivot table queries in MySQL. 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 pivot the table by exam column so as to create 1 row for each meeting_id and 1 column for each field_key, as shown below.

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

 

Automate Pivot Table Queries

If you already know the number of columns to be created, you can create pivot table queries using CASE statement to create a pivot table.

However, many times you don’t know the columns you need to create in pivot tables. In such cases, you can dynamically create pivot tables using the following query.

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when exam = ''',
      exam,
      ''' 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;

In the above statements, GROUP_CONCAT allows you to concatenate field_key values from multiple rows into single string. In the above query, we use GROUP_CONCAT to dynamically create CASE statements, based on the unique values in field_key column and store in @sql variable, which is then used to create our select query. So it helps you automate pivot table queries in MySQL

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

Now that you know how to automate pivot table queries in MySQL, you can customize them as per your requirements by adding WHERE clause or JOINS.

Also read: How to Unpivot Table in MySQL

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

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

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

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 transpose rows to columns dynamically in MySQL.

You can automate pivot table queries using a reporting tool. Here’s an example of automated pivot table created using Ubiq.

automate pivot table queries

 

Did you know you can create pivot tables in Ubiq using just drag & drop?

If you want to create pivot tables, charts, dashboards & reports from MySQL database, you can try Ubiq. We offer a 14-day free trial.

Related posts:

How to Get Records from Last 7 Days in MySQL
How to connect to MySQL on Amazon EC2 from Linux / Mac?
MySQL Compare Databases