add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tbl_data(id_data INT, nama VARCHAR(255), category VARCHAR(255));

INSERT INTO tbl_data VALUES
('110','Singkong','Asin'),('111','Ubi','Pedas'),('112','Pisang','Manis'),('113','Bawang','Asin'),('114','Tempe','Pedas Manis'),('115','Singkong','Pedas'),('116','Singkong','Manis'),('117','Singkong','Pedas Manis'),('118','Ubi','Pedas Manis'),('119','Ubi','Asin'),('120','Pisang','Pedas');

CREATE TABLE tbl_shipped(
id INT, id_data INT, DATE DATE, amount INT);

INSERT INTO tbl_shipped VALUES
('1','110','2021-05-19','40'),('2','111','2021-05-21','31'),('3','112','2021-05-29','80'),('4','112','2021-06-19','50'),('5','112','2021-06-22','5'),('6','112','2021-07-30','23'),('7','113','2021-08-05','70'),('8','113','2021-08-13','28'),('9','114','2021-09-04','8'),('10','115','2021-11-10','67'),('11','115','2021-12-20','1'),('12','117','2021-05-19','12'),('13','117','2021-06-22','50'),('14','117','2021-08-05','21'),('15','120','2021-09-04','71'),('16','120','2021-11-10','53');


SET @sql := NULL;
SET @columns := NULL;

SET @columns := (SELECT GROUP_CONCAT(CONCAT("SUM(CASE WHEN `date`='",dates,"' THEN amount ELSE 0 END) AS '",dates,"'") SEPARATOR ',') FROM (SELECT DISTINCT `date` AS dates FROM tbl_shipped) dt);

SELECT @columns;

SET @sql := CONCAT("SELECT td.id_data,
td.nama,
td.category,",@columns,"FROM tbl_shipped AS ts
JOIN tbl_data AS td ON ts.id_data=td.id_data
GROUP BY td.id_data, td.nama,
td.category;");

SELECT @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Records: 11  Duplicates: 0  Warnings: 0

Records: 16  Duplicates: 0  Warnings: 0
@columns
SUM(CASE WHEN `date`='2021-05-19' THEN amount ELSE 0 END) AS '2021-05-19',SUM(CASE WHEN `date`='2021-05-21' THEN amount ELSE 0 END) AS '2021-05-21',SUM(CASE WHEN `date`='2021-05-29' THEN amount ELSE 0 END) AS '2021-05-29',SUM(CASE WHEN `date`='2021-06-19' THEN amount ELSE 0 END) AS '2021-06-19',SUM(CASE WHEN `date`='2021-06-22' THEN amount ELSE 0 END) AS '2021-06-22',SUM(CASE WHEN `date`='2021-07-30' THEN amount ELSE 0 END) AS '2021-07-30',SUM(CASE WHEN `date`='2021-08-05' THEN amount ELSE 0 END) AS '2021-08-05',SUM(CASE WHEN `date`='2021-08-13' THEN amount ELSE 0 END) AS '2021-08-13',SUM(CASE WHEN `date`='2021-09-04' THEN amount ELSE 0 END) AS '2021-09-04',SUM(CASE WHEN `date`='2021-11-10' THEN amount ELSE 0 END) AS '2021-11-10',SUM(CASE WHEN `date`='2021-12-20' THEN amount ELSE 0 END) AS '2021-12-20'
@sql
SELECT td.id_data,
       td.nama,
       td.category,SUM(CASE WHEN `date`='2021-05-19' THEN amount ELSE 0 END) AS '2021-05-19',SUM(CASE WHEN `date`='2021-05-21' THEN amount ELSE 0 END) AS '2021-05-21',SUM(CASE WHEN `date`='2021-05-29' THEN amount ELSE 0 END) AS '2021-05-29',SUM(CASE WHEN `date`='2021-06-19' THEN amount ELSE 0 END) AS '2021-06-19',SUM(CASE WHEN `date`='2021-06-22' THEN amount ELSE 0 END) AS '2021-06-22',SUM(CASE WHEN `date`='2021-07-30' THEN amount ELSE 0 END) AS '2021-07-30',SUM(CASE WHEN `date`='2021-08-05' THEN amount ELSE 0 END) AS '2021-08-05',SUM(CASE WHEN `date`='2021-08-13' THEN amount ELSE 0 END) AS '2021-08-13',SUM(CASE WHEN `date`='2021-09-04' THEN amount ELSE 0 END) AS '2021-09-04',SUM(CASE WHEN `date`='2021-11-10' THEN amount ELSE 0 END) AS '2021-11-10',SUM(CASE WHEN `date`='2021-12-20' THEN amount ELSE 0 END) AS '2021-12-20'FROM tbl_shipped AS ts
JOIN tbl_data AS td ON ts.id_data=td.id_data
GROUP BY td.id_data, td.nama,
       td.category;		 
Statement prepared
id_data nama category 2021-05-19 2021-05-21 2021-05-29 2021-06-19 2021-06-22 2021-07-30 2021-08-05 2021-08-13 2021-09-04 2021-11-10 2021-12-20
110 Singkong Asin 40 0 0 0 0 0 0 0 0 0 0
111 Ubi Pedas 0 31 0 0 0 0 0 0 0 0 0
112 Pisang Manis 0 0 80 50 5 23 0 0 0 0 0
113 Bawang Asin 0 0 0 0 0 0 70 28 0 0 0
114 Tempe Pedas Manis 0 0 0 0 0 0 0 0 8 0 0
115 Singkong Pedas 0 0 0 0 0 0 0 0 0 67 1
117 Singkong Pedas Manis 12 0 0 0 50 0 21 0 0 0 0
120 Pisang Pedas 0 0 0 0 0 0 0 0 71 53 0