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:
1Determine 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 isCONCAT(
'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.
2Combine 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.
3Execute 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
NULLvalue on the “name” column intoTOTAL. Because we useWITH ROLLUPclause, then the “name” column in the “total” row will be filled withNULLvalue, for readability purpose, we replace theNULLvalue with the wordsTOTAL.
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_dynamicvariable, we add theCOUNTfunction to count the number of rows in thetrx_datecolumn (line 4-7). - In
@sqlvariable, we addIFNULL(name, "TOTAL") AS sales_name(line 17) to change theNULLvalue in the sales_name column into TOTAL. - We add
COUNT(trx_date) AS trx_num(line 19) to add thetrx_numcolumn. - Last, we add
SUM(amount) AS total(line 20) to add thetotalcolumn.
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.
Posts
![IVRAMANA](https://draft.blogger.com/img/profile_blank_gray.jpg" title="IVRAMANA)
0 comments:
Post a Comment