MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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

INSERT INTO testtable(date) VALUES
('2022-09-02'),
('2022-09-02'),
('2022-09-02'),
('2022-09-03');

SELECT * FROM testtable;

Records: 4  Duplicates: 0  Warnings: 0




id
date


1
2022-09-02

2
2022-09-02

3
2022-09-02

4
2022-09-03


SET @sql := NULL;

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 stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Statement prepared




2022-09-02
2022-09-03


3
1


/*let's add more data*/
INSERT INTO testtable(date) VALUES
('2022-09-04'),
('2022-09-05'),
('2022-09-05'),
('2022-09-06');

Records: 4  Duplicates: 0  Warnings: 0


/*and re-run the same query*/
SET @sql := NULL;

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 stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Statement prepared




2022-09-02
2022-09-03
2022-09-04
2022-09-05
2022-09-06


3
1
1
2
1


/*The final query stored in @sql variable*/
SELECT @sql;


@sql


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",SUM(CASE WHEN date="2022-09-04" THEN 1 ELSE 0 END) AS "2022-09-04",SUM(CASE WHEN date="2022-09-05" THEN 1 ELSE 0 END) AS "2022-09-05",SUM(CASE WHEN date="2022-09-06" THEN 1 ELSE 0 END) AS "2022-09-06" FROM testtable;


/*if you don't want to show skipped dates*/
SET @sql := NULL;

SELECT CONCAT('SELECT ',GROUP_CONCAT(cols),' FROM testtable;') INTO @sql
   FROM
(SELECT 
      CONCAT('SUM(CASE WHEN date="',date,'" THEN 1 ELSE 0 END) AS "',date,'"') AS cols
  FROM testtable
GROUP BY date) v;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Statement prepared




2022-09-02
2022-09-03
2022-09-04
2022-09-05
2022-09-06


3
1
1
2
1


/*let's try with skipped dates*/
INSERT INTO testtable(date) VALUES
('2022-09-08'),
  /*skipped 9*/
('2022-09-10'),
  /*skipped 11 to 14*/
('2022-09-15'),
('2022-09-16');

Records: 4  Duplicates: 0  Warnings: 0


/*this is what recursive cte will produce - 
notice that even skipped dates are shown in the results*/
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


mindt
maxdt


2022-09-02
2022-09-16

2022-09-03
2022-09-16

2022-09-04
2022-09-16

2022-09-05
2022-09-16

2022-09-06
2022-09-16

2022-09-07
2022-09-16

2022-09-08
2022-09-16

2022-09-09
2022-09-16

2022-09-10
2022-09-16

2022-09-11
2022-09-16

2022-09-12
2022-09-16

2022-09-13
2022-09-16

2022-09-14
2022-09-16

2022-09-15
2022-09-16

2022-09-16
2022-09-16

mindt	maxdt
2022-09-02	2022-09-16
2022-09-03	2022-09-16
2022-09-04	2022-09-16
2022-09-05	2022-09-16
2022-09-06	2022-09-16
2022-09-07	2022-09-16
2022-09-08	2022-09-16
2022-09-09	2022-09-16
2022-09-10	2022-09-16
2022-09-11	2022-09-16
2022-09-12	2022-09-16
2022-09-13	2022-09-16
2022-09-14	2022-09-16
2022-09-15	2022-09-16
2022-09-16	2022-09-16