clear markdown compare help best fiddles feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601434 fiddles created (48010 in the last week).

CREATE TABLE appointment (id int ,start_datetime date ,location_id int)
 hidden batch(es)


INSERT INTO appointment VALUES (1,'2021-06-01',1),(2,'2021-06-01',1),(3,'2021-06-02',1), (4,'2021-06-01',2),(5,'2021-06-02',2),(6,'2021-06-02',2)
 hidden batch(es)


CREATE TABLE location (id int , name varchar(10))
 hidden batch(es)


INSERT INTO location VALUES (1, 'loc1'),(2,'loc2')
 hidden batch(es)


SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT('SUM(IF(a.start_datetime = ''', `start_datetime`, ''', 1,0)) AS ''',`start_datetime`,'''') ) INTO @sql FROM appointment ORDER BY `start_datetime`;
 hidden batch(es)


SET @sql = CONCAT('SELECT l.`name`, ', @sql, ' FROM appointment a INNER JOIN location l ON l.id = a.location_id GROUP BY l.id,l.`name` ORDER BY l.id'); PREPARE stmt FROM @sql; EXECUTE stmt;
name 2021-06-01 2021-06-02
loc1 2 1
loc2 1 2
 hidden batch(es)