Dynamic Pivot Table Using MySQL – Simple Logic

In this tutorial, we’ll discuss how to create a dynamic pivot table using MySQL.

This tutorial is part of the previous tutorial: Pivot Table Using MySQL. In that tutorial, we have discussed how to create a pivot table with pure SQL query, that query can only be used for fixed (static) columns.

In certain circumstances, the data that we used for columns changes dynamically, so that the number of columns in the pivot table also changes, therefore the static SQL that we have discussed previously could not be used anymore.

I. The Preparation

Because this tutorial is a continuation of the previous tutorial, the data that we use is the same as the previous, which is looks like the following table:

trx_id nama trx_date team amout
1 Alfa 2016-01-10 1 250
2 Charlie 2016-01-02 2 175
3 Bravo 2016-01-01 1 310
4 Bravo 2016-02-04 1 250
5 Alfa 2016-01-15 1 300
6 Charlie 2016-01-13 2 325
7 Bravo 2016-02-07 1 275
8 Bravo 2016-03-06 1 150
9 Alfa 2016-02-05 1 215
10 Alfa 2016-02-22 1 350
11 Alfa 2016-02-02 1 450
12 Alfa 2016-03-12 1 150
13 Alfa 2016-03-17 1 225
14 Bravo 2016-03-11 1 150
15 Bravo 2016-03-18 1 150
16 Charlie 2016-01-23 2 350
17 Charlie 2016-02-22 2 300
18 Charlie 2016-03-21 2 275
19 Charlie 2016-03-12 2 450
20 Delta 2016-02-11 2 450
21 Delta 2016-02-17 2 550
22 Delta 2016-03-11 2 370

You can download the SQL file and dump it to your database using database managers such as phpMyAdmin, Heidi SQL or Toad for MySQL

II. Dynamic Pivot Table Using MySQL

After the data is ready, now we’ll create a dynamic pivot table using MySQL. To create a dynamic pivot table, we have to create dynamic SQL.

Because SQL is not a dynamic language (such as PHP that has loops statements – for, while, etc.), so we can not generate SQL query dynamically.

To solve this, we need to do some workaround, we create a SQL command using SELECT statement just like when we want to retrieve data from a database.

The query look like this:

SET @sql_dynamic = (
	SELECT
		GROUP_CONCAT( DISTINCT
			CONCAT(
				'SUM( IF(MONTH(trx_date) = '
				, MONTH(trx_date)
				, ', amount,0) ) AS mo_'
				, MONTH(trx_date)
			)
		)
	FROM sales_table
);

SET @sql = CONCAT('SELECT name, ', 
			  @sql_dynamic, ' 
		   FROM sales_table
		   GROUP BY name WITH ROLLUP'
	   );
	 
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

The result:

+---------+------+------+------+
| name    | mo_1 | mo_2 | mo_3 |
+---------+------+------+------+
| Alfa    |  375 |  815 |  375 |
| Bravo   |  310 |  525 |  825 |
| Charlie |  850 |  300 |  725 |
| Delta   |    0 | 1000 |  370 |
| NULL    | 1535 | 2640 | 2295 |
+---------+------+------+------+

In the above query, the number of columns will grow if there are additional transactions in April, May, June, and so on … If transformed into static SQL, the above query would look like this:

SELECT  name,
	SUM( IF(MONTH(trx_date) = 1, amount,0) ) AS mo_1,
	SUM( IF(MONTH(trx_date) = 2, amount,0) ) AS mo_2,
	SUM( IF(MONTH(trx_date) = 3, amount,0) ) AS mo_3
FROM sales_table
GROUP BY name
WITH ROLLUP

In order to easy to understand how to create SQL query dynamically, here are some useful guidelines:

1 Determine which part of SQL query that is dynamic

The first step is write down the static SQL, then, identify which part of the query that is dynamic, in the example above, the dynamic part is line 2-4, as follows:

SELECT  name,
	SUM( IF(MONTH(trx_date) = 1, amount,0) ) AS mo_1,
	SUM( IF(MONTH(trx_date) = 2, amount,0) ) AS mo_2,
	SUM( IF(MONTH(trx_date) = 3, amount,0) ) AS mo_3
FROM sales_table
GROUP BY name
WITH ROLLUP

Next, build a query in such a way so that it will generate a query that same as line 2-4 above, then, save the query in a variable. The query will look like this:

SET @sql_dynamic = (
	SELECT
		GROUP_CONCAT( DISTINCT
			CONCAT(
				'SUM( IF(MONTH(trx_date) = '
				, MONTH(trx_date)
				, ', amount,0) ) AS mo_'
				, MONTH(trx_date)
			)
		)
	FROM sales_table
);

In the above example, we store the SQL query into a variable named @sql_dynamic. The main part of the query is

CONCAT(
	'SUM( IF(MONTH(trx_date) = '
	, MONTH(trx_date)
	, ', amount,0) ) AS mo_'
	, MONTH(trx_date)
)

Tt will generate a table like this:

+--------------------------------------------------+
| query                                            |
+--------------------------------------------------+
| SUM( IF(MONTH(trx_date) = 1, amount,0) ) AS mo_1 |
| SUM( IF(MONTH(trx_date) = 1, amount,0) ) AS mo_1 |
| SUM( IF(MONTH(trx_date) = 1, amount,0) ) AS mo_1 |
| SUM( IF(MONTH(trx_date) = 2, amount,0) ) AS mo_2 |
| SUM( IF(MONTH(trx_date) = 1, amount,0) ) AS mo_1 |
| SUM( IF(MONTH(trx_date) = 1, amount,0) ) AS mo_1 |
| SUM( IF(MONTH(trx_date) = 2, amount,0) ) AS mo_2 |
| SUM( IF(MONTH(trx_date) = 3, amount,0) ) AS mo_3 |
| SUM( IF(MONTH(trx_date) = 2, amount,0) ) AS mo_2 |
| SUM( IF(MONTH(trx_date) = 2, amount,0) ) AS mo_2 |
| SUM( IF(MONTH(trx_date) = 2, amount,0) ) AS mo_2 |
| SUM( IF(MONTH(trx_date) = 3, amount,0) ) AS mo_3 |
| SUM( IF(MONTH(trx_date) = 3, amount,0) ) AS mo_3 |
| SUM( IF(MONTH(trx_date) = 3, amount,0) ) AS mo_3 |
| SUM( IF(MONTH(trx_date) = 3, amount,0) ) AS mo_3 |
| SUM( IF(MONTH(trx_date) = 1, amount,0) ) AS mo_1 |
| SUM( IF(MONTH(trx_date) = 2, amount,0) ) AS mo_2 |
| SUM( IF(MONTH(trx_date) = 3, amount,0) ) AS mo_3 |
| SUM( IF(MONTH(trx_date) = 3, amount,0) ) AS mo_3 |
| SUM( IF(MONTH(trx_date) = 2, amount,0) ) AS mo_2 |
| SUM( IF(MONTH(trx_date) = 2, amount,0) ) AS mo_2 |
| SUM( IF(MONTH(trx_date) = 3, amount,0) ) AS mo_3 |
+--------------------------------------------------+

Next, we add a DISTINCT clause in the front of CONCAT clause to remove the duplicate rows, so the table will look like the following:

+--------------------------------------------------+
| query                                            |
+--------------------------------------------------+
| SUM( IF(MONTH(trx_date) = 1, amount,0) ) AS mo_1 |
| SUM( IF(MONTH(trx_date) = 2, amount,0) ) AS mo_2 |
| SUM( IF(MONTH(trx_date) = 3, amount,0) ) AS mo_3 |
+--------------------------------------------------+

Last, we use a GROUP_CONCAT function to combine all rows with comma ( , ) separator. The result looks like the following:

+----------------------------------------------------------------------------------------------------------------------------------------------------+
| query                                                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------------------+
| SUM( IF(MONTH(trx_date) = 1, amount,0) ) AS mo_1,SUM( IF(MONTH(trx_date) = 2, amount,0) ) AS mo_2,SUM( IF(MONTH(trx_date) = 3, amount,0) ) AS mo_3 |
+----------------------------------------------------------------------------------------------------------------------------------------------------+

Furthermore, don’t forget to test whether the query result meets our expectation. Run the command:

SELECT @sql_dynamic;

The Result:

+----------------------------------------------------------------------------------------------------------------------------------------------------+
| @sql_dynamic                                                                                                                                       |
+----------------------------------------------------------------------------------------------------------------------------------------------------+
| SUM( IF(MONTH(trx_date) = 1, amount,0) ) AS mo_1,SUM( IF(MONTH(trx_date) = 2, amount,0) ) AS mo_2,SUM( IF(MONTH(trx_date) = 3, amount,0) ) AS mo_3 |
+----------------------------------------------------------------------------------------------------------------------------------------------------+

The result already meets our expectation.

2 Combine the dynamic query with the static query

After we save the dynamic query in a variable, then, we combine that with another query, so we’ll have a complete working query, we store the completed query in a variable:

SET @sql = CONCAT('SELECT name, ', 
		@sql_dynamic, ' 
	FROM sales_table
	GROUP BY name WITH ROLLUP'
);

In the above example, we store the entire query into the variable @sql. As usual, check whether the contents of the variable meets out expectation, run the following query:

SELECT @sql;

The result:

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @sql                                                                                                                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT name, SUM( IF(MONTH(trx_date) = 1, amount,0) ) AS mo_1,SUM( IF(MONTH(trx_date) = 2, amount,0) ) AS mo_2,SUM( IF(MONTH(trx_date) = 3, amount,0) ) AS mo_3
   FROM sales_table
   GROUP BY name WITH ROLLUP |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The result meets what we expected.

3 Execute the query

After we build the complete query, finally, we run the query. Because the query is stored in a variable, we can not directly execute it, instead, use PREPARE statement.

The query:

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

In the query above, we named the PREPARE statement with stmt which is short of a word: statement, you are free to give another name.

Using the FROM clause, we fill the stmt with queries that we have stored in the @sql variable.

Next, we run the EXECUTE statement to execute the SQL statement saved in the PREPARE statement. In the above example, we execute SQL saved in the stmt prepared statement.

At this step, we’ll get the result that we expected. In addition, we can run the DEALLOCATE PREPARE statement that will release or remove the PREPARE statement, in this case stmt

This is useful to reduce the number of stored PREPARE statement which is limited to a certain amount, usually 16382.

III. Dynamic Pivot Tables Using MySQL – Adds New Columns

After we understand how to create a dynamic pivot table using MySQL, then let’s make some improvements by:

  • Adding columns that contain the number of transactions per month, per name.
  • Add a TOTAL column that contains the total amount of each sales name.
  • Changing the NULL value on the “name” column into TOTAL. Because we use WITH ROLLUP clause, then the “name” column in the “total” row will be filled with NULL value, for readability purpose, we replace the NULL value with the words TOTAL.

The query:

SET @sql_dynamic = (
		SELECT
			GROUP_CONCAT( DISTINCT
				CONCAT('COUNT( IF(MONTH(trx_date) = '
					, MONTH(trx_date)
					, ', amount, NULL) ) AS trx_'
					, MONTH(trx_date)
					, ', SUM( IF(MONTH(trx_date) = '
					, MONTH(trx_date)
					, ', amount, 0) ) AS mo_'
					, MONTH(trx_date)
				)
			)
		FROM sales_table
	);

SET @SQL = CONCAT('SELECT IFNULL(name, "TOTAL") AS sales_name, ', 
			  @sql_dynamic, ', 
			  COUNT(trx_date) AS trx_num,
			  SUM(amount) AS total 
		   FROM sales_table
		   GROUP BY name 
		   WITH ROLLUP'
	);

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

The result:

+------------+-------+------+-------+------+-------+------+---------+-------+
| sales_name | trx_1 | mo_1 | trx_2 | mo_2 | trx_3 | mo_3 | trx_num | total |
+------------+-------+------+-------+------+-------+------+---------+-------+
| Alfa       |     2 |  375 |     3 |  815 |     2 |  375 |       7 |  1565 |
| Bravo      |     1 |  310 |     2 |  525 |     3 |  825 |       6 |  1660 |
| Charlie    |     3 |  850 |     1 |  300 |     2 |  725 |       6 |  1875 |
| Delta      |     0 |    0 |     2 | 1000 |     1 |  370 |       3 |  1370 |
| TOTAL      |     6 | 1535 |     8 | 2640 |     8 | 2295 |      22 |  6470 |
+------------+-------+------+-------+------+-------+------+---------+-------+

Explanation:

  • In the @sql_dynamic variable, we add the COUNT function to count the number of rows in the trx_date column (line 4-7).
  • In @sql variable, we add IFNULL(name, "TOTAL") AS sales_name (line 17) to change the NULL value in the sales_name column into TOTAL.
  • We add COUNT(trx_date) AS trx_num (line 19) to add the trx_num column.
  • Last, we add SUM(amount) AS total (line 20) to add the total column.

If translated into a static query, the above query will look like the following:

SELECT  IFNULL(name, "TOTAL") AS name_sales, 
	COUNT( IF(MONTH(trx_date) = 1, amount, NULL) ) AS trx_1, 
	SUM( IF(MONTH(trx_date) = 1, amount, 0) ) AS mo_1,
	COUNT( IF(MONTH(trx_date) = 2, amount, NULL) ) AS trx_2,
	SUM( IF(MONTH(trx_date) = 2, amount, 0) ) AS mo_2,
	COUNT( IF(MONTH(trx_date) = 3, amount, NULL) ) AS trx_3,
	SUM( IF(MONTH(trx_date) = 3, amount, 0) ) AS mo_3, 
	COUNT(trx_date) AS trx_num,
	SUM(amount) AS total 
FROM sales_table
GROUP BY name 
WITH ROLLUP

In practice, the condition can be vary, the above query can be used as an example/inspiration in making various kinds of dynamic queries to build dynamic pivot table that meets the conditions.

IV. Conclusion

Although SQL is not a dynamic language, with a little workaround, we can create a dynamic pivot table using MySQL.

The point is: To create a pivot table, first, determine which part of  the query that is dynamic, then, combine the dynamic query with other queries.

That’all, we have learned how to create a dynamic pivot table using MySQL.

Related Post

Leave a comment

Like Us

Social