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;
