mysql – possible to pivot across a dynamic date column?

If you are using MySQL v8 that supports recursive common table expression (cte), then you can combine that with prepared statement. I assume that the sample data you’re provided in the question does not represent the real data you have especially the date format so let’s say that your table is like this:

Whereby the date value is following the standard MySQL date format, here’s a CREATE TABLE syntax:

CREATE TABLE testtable (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  date DATE);

Now, it seems like you know how to pivot the result using query so I assume it would look something like this:

SELECT SUM(CASE WHEN date="2022-09-02" THEN 1 ELSE 0 END) AS "2022-09-02",
       SUM(CASE WHEN date="2022-09-03" THEN 1 ELSE 0 END) AS "2022-09-03" 
FROM testtable;

If your query is nothing like the above then establish your own final structure of the query before you start. This will help you in shaping the query for the prepared statement.

The first step I’m doing here is generating the dynamic columns based on the date value. I’m using RECURSIVE cte here with the assumption that even skipped dates will still be shown as zeros:

WITH RECURSIVE cte AS (
  SELECT MIN(date) AS mindt, MAX(date) AS maxdt 
     FROM testtable 
UNION 
  SELECT mindt + INTERVAL 1 DAY, maxdt
     FROM cte 
  WHERE mindt + INTERVAL 1 DAY <= maxdt)
SELECT * FROM cte

Based on your sample data, the result will return like this:

The column that we will use for the rest of the process is just mindt. Column maxdt here is just for the cte reference on when to stop generating the running date.

Note: If you don’t wish to show skipped dates then you don’t need to use RECURSIVE cte and your final query should able to use on older MySQL versions as well. I’ll include that option later in this answer.

Once we have that, we can start building our query for the prepared statement. Let’s generate SUM(CASE WHEN date="2022-09-02" THEN 1 ELSE 0 END) AS "2022-09-02" (refer the final query above) from the RECURSIVE cte statement, so:

.....
 SELECT CONCAT('SUM(CASE WHEN date="',mindt,'" THEN 1 ELSE 0 END) AS "',mindt,'"') 
    FROM cte

This is what you’ll get from that:

We’ll do a GROUP_CONCAT() statement to make those results returned as a single row:

 SELECT GROUP_CONCAT(CONCAT('SUM(CASE WHEN date="',mindt,'" THEN 1 ELSE 0 END) AS "',mindt,'"'))
    FROM cte

This is what you’ll get:

Now, the result above looks like the columns I’m aiming for the final query. I’ll add another CONCAT() to add SELECT and FROM table_name like so:

...
SELECT CONCAT('SELECT ',
     GROUP_CONCAT(
      CONCAT('SUM(CASE WHEN date="',mindt,'" THEN 1 ELSE 0 END) AS "',mindt,'"')),
     ' FROM testtable;')
  FROM cte;

This will return result of a query exactly the same as the one I’m aiming for my final query above. What I’ll do next is just to insert the result of that into a variable and do the prepared statement operation. Let’s say @sql is my variable:

SET @sql := NULL; /*setting as NULL just in case the variable is not empty*/

WITH RECURSIVE cte AS (
  SELECT MIN(date) AS mindt, MAX(date) AS maxdt 
     FROM testtable 
UNION 
  SELECT mindt + INTERVAL 1 DAY, maxdt
     FROM cte 
  WHERE mindt + INTERVAL 1 DAY <= maxdt)
SELECT CONCAT('SELECT ',
  GROUP_CONCAT(
      CONCAT('SUM(CASE WHEN date="',mindt,'" THEN 1 ELSE 0 END) AS "',mindt,'"')),
     ' FROM testtable;') INTO @sql
  FROM cte;

/*prepare, execute then deallocate the statement generated from @sql variable*/
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

The good thing about this is, if you’re just adding data into the table and conditions are still the same – to pivot and count occurrence by date – you don’t need to change the query at all.

Check this fiddle for more examples and explanation including the option to exclude skipped dates in the result.