has_many :codes

MySQL dynamic column name

Published  

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:

SELECT license_id, collect_date, MAX(total_hv_cores) cores
FROM hcu_collection
WHERE collect_date >= SUBDATE(CURDATE(), 2)
GROUP BY license_id, collect_date
ORDER BY collect_date ASC, collect_hour ASC;

which produces these results:

+------------+--------------+-------+
| license_id | collect_date | cores |
+------------+--------------+-------+
| 18 | 2015-12-04 | 1108 |
| 67 | 2015-12-04 | 436 |
| 102 | 2015-12-04 | 140 |
...
...
| 12174 | 2015-12-10 | 78 |
| 12380 | 2015-12-10 | 624 |
...
...

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

+------------+-----------+-----------+-----------+
| license_id | Tue 08/12 | Wed 09/12 | Thu 10/12 |
+------------+-----------+-----------+-----------+
| 2 | 238 | 238 | 246 |
| 3 | 60 | 68 | 68 |
| 4 | 12 | 16 | 12 |
| 7 | 212 | 212 | 220 |
...
...

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.

DROP TABLE IF EXISTS tmp_results;

CREATE TEMPORARY TABLE tmp_results AS
SELECT license_id, collect_date, MAX(total_hv_cores) cores
FROM hcu_collection
WHERE collect_date >= SUBDATE(CURDATE(), 2)
GROUP BY license_id, collect_date
ORDER BY collect_date ASC, collect_hour ASC;

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

SELECT CONCAT('
SELECT license_id, ',cores_by_dates,'
FROM tmp_results
GROUP BY license_id
ORDER BY license_id'
)
INTO @query
FROM
(
SELECT GROUP_CONCAT(CONCAT('IFNULL(MAX(CASE WHEN collect_date=''',actual_date,''' THEN cores END), ''-'') AS "',col_name,'"')) cores_by_dates
FROM (
SELECT actual_date, DATE_FORMAT(actual_date,'%a %d/%m') AS col_name
FROM (SELECT DISTINCT collect_date AS actual_date FROM tmp_results) AS dates
) dates_with_col_names
) result;

The important bit is

SELECT GROUP_CONCAT(CONCAT('IFNULL(MAX(CASE WHEN collect_date=''',actual_date,''' THEN cores END), ''-'') AS "',col_name,'"')) cores_by_dates
FROM (
SELECT actual_date, DATE_FORMAT(actual_date,'%a %d/%m') AS col_name
FROM (SELECT DISTINCT collect_date AS actual_date FROM tmp_results) AS dates
) dates_with_col_names

which generates something like:

IFNULL(MAX(CASE WHEN collect_date='2015-12-08' THEN cores END), '-') AS "Tue 08/12",IFNULL(MAX(CASE WHEN collect_date='2015-12-09' THEN cores END), '-') AS "Wed 09/12",IFNULL(MAX(CASE WHEN collect_date='2015-12-10' THEN cores END), '-') AS "Thu 10/12"

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

PREPARE statement FROM @query;

Last, we just need to execute it:

EXECUTE statement;

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

DEALLOCATE PREPARE statement;

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

SET SESSION group_concat_max_len = 1000000;

before the dynamic generation of the query.

Hope it can be useful to someone.

© Vito Botta