By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table1 (
id int,
period DATE,
value INT,
name varchar(255)
);
INSERT INTO table1 VALUES
(1,'2020-06-01',3,'anna'),
(2,'2020-06-01',2,'anna'),
(3,'2020-06-01',3,'anna'),
(4,'2020-06-01',1,'juned'),
(5,'2020-06-01',3,'juned'),
(6,'2020-06-01',2,'juned'),
(7,'2020-07-01',3,'anna'),
(8,'2020-07-01',2,'anna'),
(9,'2020-07-01',2,'anna'),
(10,'2020-07-01',3,'juned'),
(11,'2020-07-01',3,'juned'),
(12,'2020-07-01',3,'juned');
Records: 12 Duplicates: 0 Warnings: 0
SELECT period, name, SUM(CASE WHEN value = 3 THEN 1 ELSE 0 END) AS value FROM t1
GROUP BY period, name;
Table 'fiddle.t1' doesn't exist
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT 'SUM(value = 3 AND period = ''', period, ''') AS `', period, '`')
INTO @sql
FROM table1;
SET @sql = CONCAT('SELECT name, ', @sql, ' FROM table1 GROUP BY name');
select @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
| @sql |
|---|
| SELECT name, SUM(value = 3 AND period = '2020-06-01') AS `2020-06-01`,SUM(value = 3 AND period = '2020-07-01') AS `2020-07-01` FROM table1 GROUP BY name |
Statement prepared
| name | 2020-06-01 | 2020-07-01 |
|---|---|---|
| anna | 2 | 1 |
| juned | 1 | 3 |