By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS `schedule` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`spec_id` int(11) DEFAULT NULL,
`data` datetime DEFAULT NULL,
`reserved` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13289 DEFAULT CHARSET=utf8;
INSERT INTO `schedule` (`id`, `spec_id`, `data`, `reserved`) VALUES
(8359, 506, '2019-11-12 18:00:00', 0),
(8360, 506, '2019-11-12 18:30:00', 0),
(8361, 506, '2019-11-12 19:00:00', 0),
(8362, 506, '2019-11-12 19:30:00', 0),
(8363, 506, '2019-11-12 20:00:00', 0),
(8364, 506, '2019-11-12 20:30:00', 0),
(8365, 506, '2019-11-14 18:00:00', 0),
(8366, 506, '2019-11-14 18:30:00', 0),
(8367, 506, '2019-11-14 19:00:00', 0),
(8368, 506, '2019-11-14 19:30:00', 0),
(8369, 506, '2019-11-14 20:00:00', 0),
(8370, 506, '2019-11-14 20:30:00', 0),
(8371, 506, '2019-11-19 18:00:00', 0),
(8372, 506, '2019-11-19 18:30:00', 0),
(8373, 506, '2019-11-19 19:00:00', 0),
(8374, 506, '2019-11-19 19:30:00', 0),
(8375, 506, '2019-11-19 20:00:00', 0),
(8376, 506, '2019-11-19 20:30:00', 0),
(8377, 506, '2019-11-21 18:00:00', 0),
(8378, 506, '2019-11-21 18:30:00', 0),
(8379, 506, '2019-11-21 19:00:00', 0),
(8380, 506, '2019-11-21 19:30:00', 0),
(8381, 506, '2019-11-21 20:00:00', 0),
(8382, 506, '2019-11-21 20:30:00', 0),
(8383, 506, '2019-11-26 18:00:00', 0),
(8384, 506, '2019-11-26 18:30:00', 0),
(8385, 506, '2019-11-26 19:00:00', 0),
select
id,
name,
substring_index(substring_index(col1, ',', n), ',', -1) as '12-11-2019',
substring_index(substring_index(col2, ',', n), ',', -1) as '14-11-2019',
substring_index(substring_index(col3, ',', n), ',', -1) as '19-11-2019',
n
from
(select
spec.id,
`name`,
group_concat(distinct if(left(schedule.data,10) = '2019-11-12', DATE_FORMAT(schedule.data,'%H:%i'),null)) as col1,
group_concat(distinct if(left(schedule.data,10) = '2019-11-14', DATE_FORMAT(schedule.data,'%H:%i'),null)) as col2,
group_concat(distinct if(left(schedule.data,10) = '2019-11-19', DATE_FORMAT(schedule.data,'%H:%i'),null)) as col3
from
service_spec inner join spec on spec.id = service_spec.spec_id
left join schedule on service_spec.spec_id = schedule.spec_id
where
spec.id = 506) as main
join (SELECT @row := @row + 1 as n
FROM (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t
join (SELECT @row:=0) r ) as s_no
on char_length(col1) - char_length(replace(col1, ',', '')) >= n - 1;
| id | name | 12-11-2019 | 14-11-2019 | 19-11-2019 | n |
|---|---|---|---|---|---|
| 506 | Spec1 | 18:00 | 18:00 | 18:00 | 1 |
| 506 | Spec1 | 18:30 | 18:30 | 18:30 | 2 |
| 506 | Spec1 | 19:00 | 19:00 | 19:00 | 3 |
| 506 | Spec1 | 19:30 | 19:30 | 19:30 | 4 |
| 506 | Spec1 | 20:00 | 20:00 | 20:00 | 5 |
| 506 | Spec1 | 20:30 | 20:30 | 20:30 | 6 |