MySQL dynamic column name

This post describes how to dynamically generate column names from row values in MySQL databases, also known as pivot tables.

Let’s say you have a table or the results of a query with some values by date in different rows. You want to generate column names dynamically from these dates, and show the relevant results as values for these new columns instead of values in different rows. So you basically want to transpose rows into columns and have dynamically generated column names at the same time. Unfortunately MySQL doesn’t allow the use of functions to generate column names, and as far as I know it doesn’t have a means out of the box to generate column names dynamically in general (please let me know if I am mistaken; I’m keen to learn something new), but it is definitely possible at least with a trick using prepared statements. Let me show you with an example.

I have a table, named hcu_collection, which I use to collect some data for each of a number of software licenses. The relevant columns in this example are the license_id, the collect_date and an integer column named total_hv_cores (it’s just an example from a real app so ignore the meaning of this column as it’s not important); for example I want to know the MAX(total_hv_cores) by date for each license over the past 3 days. So I can use a simple query like the following:

 

What I want instead is a table that looks like the following, for example for the past 3 days:

 

As said I am not aware if MySQL already has some means to achieve this, so the way I have done it is by generating a query dynamically which, when executed, will then generate the column names from the dates as I want.

The first step is to create a temporary table with the results from the original query, for convenience, since we are going to need these results more than once in the query that will be generated dynamically.

Next, we need to generate a new query dynamically. Here’s an example:

 

The important bit is

 

which generates something like:

 

We save this new query in @query so that we can use it to prepare a statement:

Last, we just need to execute it:

This shows the results I want, with the dates as column names. Don’t forget to deallocate the prepared statement after fetching the results:

Note: depending on how many dates you use to generate the columns, you may exceed the limit allowed for GROUP_CONCAT‘s length (default is 1024 bytes). So you may need to add something like

 

before the dynamic generation of the query.

Hope it can be useful to someone.

About the author

Vito Botta

I am a passionate web developer based in Espoo, Finland. Besides computing, I love boxing and good food!

View all posts

3 Comments

  • Hi again Vito, I think this example is relevant for the type of query I wish to execute, but cannot relate properly with the schema assumed here and so the result seems to elude. Can you share a sample schema or maybe a sample table so I can run the query and simulate one for my schema on similar lines. Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *

one × 4 =