Web Development Resources

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:

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.

11 Responses

Thanks for posting this. This is one of the cleanest solutions that I have found so far to create a dynamic pivot query. I still can’t believe that this kind of function can’t be performed natively in SQL o\

But i get an empty set. what am i doing wrong? Also, I first tried it on my mamp mysql and I got a fatal error. I then tried with a online server database and didn’t get the fatal error messgae, but only an empty set.