mysql - possible to pivot across a dynamic date column?-CodePudding

consider the table

date	id
9/2	1
9/2	2
9/2	3
9/3	4

this can be pivoted up using count and case statements for each date

9/2	9/3
3	1

but is it possible to do the same thing without knowledge of the dates present in date column?

CodePudding user response：

Yes, it is possible using the GROUP BY clause. As the clause name indicates, you can group the rows by a certain column or set of columns. You can combine that with the COUNT function to achieve your goal.

SELECT date, COUNT(*)
FROM TableName
GROUP BY date

CodePudding user response：

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:

id	date
1	2022-09-02
2	2022-09-02
3	2022-09-02
4	2022-09-03

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:

mindt	maxdt
2022-09-02	2022-09-16
2022-09-03	2022-09-16

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:

CONCAT('SUM(CASE WHEN date="',mindt,'" THEN 1 ELSE 0 END) AS "',mindt,'"')
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"

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:

GROUP_CONCAT(CONCAT('SUM(CASE WHEN date="',mindt,'" THEN 1 ELSE 0 END) AS "',mindt,'"'))
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"

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.