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.
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.
