MySQL 8.4

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

CREATE TABLE treatments (
  treatment_id INT,
  patient_id   INT,
  treatment    VARCHAR(50),
  due_time     INT,
  status       INT,
  treatment_schedule_id INT
);
INSERT INTO treatments (treatment_id, patient_id, treatment, due_time, status, treatment_schedule_id)
VALUES (13, 91,'co-amox 2.3ml IV', 16, 0, 1), 
(13, 91,'co-amox 2.3ml IV', 14, 0, 3), 
(13, 91,'co-amox 2.3ml IV', 12, 0, 4), 
(13, 91,'co-amox 2.3ml IV', 13, 0, 9), 
(13, 91,'co-amox 2.3ml IV', 6, 0, 10), 
(13, 91,'co-amox 2.3ml IV', 4, 0, 11), 
(13, 91,'co-amox 2.3ml IV', 22, 0, 12), 
(13, 91,'co-amox 2.3ml IV', 18, 0, 13), 
(14, 91,'metacam 4.8kg dose PO SID', 22, 0, 14), 
(14, 91,'metacam 4.8kg dose PO SID', 1, 0, 15), 
(14, 91,'metacam 4.8kg dose PO SID', 7, 0, 16), 
(14, 91,'metacam 4.8kg dose PO SID', 6, 0, 17);

Records: 12  Duplicates: 0  Warnings: 0


SELECT JSON_ARRAY(
       treatment_id,
       patient_id,
       treatment,
       status,
       JSON_ARRAYAGG(JSON_ARRAY(due_time, treatment_schedule_id))
       ) AS treatment
FROM   treatments
GROUP BY
       patient_id,
       treatment_id,
       treatment,
       status;


treatment


[13, 91, "co-amox 2.3ml IV", 0, [[16, 1], [14, 3], [12, 4], [13, 9], [6, 10], [4, 11], [22, 12], [18, 13]]]

[14, 91, "metacam 4.8kg dose PO SID", 0, [[22, 14], [1, 15], [7, 16], [6, 17]]]


SELECT treatment_id,
       patient_id,
       treatment,
       status,
       MAX(CASE due_time WHEN  0 THEN treatment_schedule_id END) AS "00",
       MAX(CASE due_time WHEN  1 THEN treatment_schedule_id END) AS "01",
       MAX(CASE due_time WHEN  2 THEN treatment_schedule_id END) AS "02",
       MAX(CASE due_time WHEN  3 THEN treatment_schedule_id END) AS "03",
       MAX(CASE due_time WHEN  4 THEN treatment_schedule_id END) AS "04",
       MAX(CASE due_time WHEN  5 THEN treatment_schedule_id END) AS "05",
       MAX(CASE due_time WHEN  6 THEN treatment_schedule_id END) AS "06",
       MAX(CASE due_time WHEN  7 THEN treatment_schedule_id END) AS "07",
       MAX(CASE due_time WHEN  8 THEN treatment_schedule_id END) AS "08",
       MAX(CASE due_time WHEN  9 THEN treatment_schedule_id END) AS "09",
       MAX(CASE due_time WHEN 10 THEN treatment_schedule_id END) AS "10",
       MAX(CASE due_time WHEN 11 THEN treatment_schedule_id END) AS "11",
       MAX(CASE due_time WHEN 12 THEN treatment_schedule_id END) AS "12",
       MAX(CASE due_time WHEN 13 THEN treatment_schedule_id END) AS "13",
       MAX(CASE due_time WHEN 14 THEN treatment_schedule_id END) AS "14",
       MAX(CASE due_time WHEN 15 THEN treatment_schedule_id END) AS "15",
       MAX(CASE due_time WHEN 16 THEN treatment_schedule_id END) AS "16",
       MAX(CASE due_time WHEN 17 THEN treatment_schedule_id END) AS "17",
       MAX(CASE due_time WHEN 18 THEN treatment_schedule_id END) AS "18",
       MAX(CASE due_time WHEN 19 THEN treatment_schedule_id END) AS "19",
       MAX(CASE due_time WHEN 20 THEN treatment_schedule_id END) AS "20",
       MAX(CASE due_time WHEN 21 THEN treatment_schedule_id END) AS "21",
       MAX(CASE due_time WHEN 22 THEN treatment_schedule_id END) AS "22",
       MAX(CASE due_time WHEN 23 THEN treatment_schedule_id END) AS "23"
FROM   treatments
GROUP BY
       patient_id,
       treatment_id,
       treatment,
       status;


treatment_id
patient_id
treatment
status
00
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23


13
91
co-amox 2.3ml IV
0
null
null
null
null
11
null
10
null
null
null
null
null
4
9
3
null
1
null
13
null
null
null
12
null

14
91
metacam 4.8kg dose PO SID
0
null
15
null
null
null
null
17
16
null
null
null
null
null
null
null
null
null
null
null
null
null
null
14
null

treatment_id	patient_id	treatment	status	00	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23
13	91	co-amox 2.3ml IV	0	null	null	null	null	11	null	10	null	null	null	null	null	4	9	3	null	1	null	13	null	null	null	12	null
14	91	metacam 4.8kg dose PO SID	0	null	15	null	null	null	null	17	16	null	null	null	null	null	null	null	null	null	null	null	null	null	null	14	null