[RESOLVED] MySQL: Date rows as columns

[dee-u]

This is my current table design:
Attachment 175817

I want to display the data like this:
Attachment 175819

I am able to convert the columns as rows using UNION ALL, as of now I am trying to convert the rows into columns and combine them, just putting it up here if in case someone is bored and would like to help out. Here's the table script just in case:

CREATE TABLE `puimunicipalities` (
  `ID` int(11) NOT NULL,
  `ReportDate` datetime DEFAULT NULL,
  `Alilem` int(11) DEFAULT '0',
  `Banayoyo` int(11) DEFAULT '0',
  `Bantay` int(11) DEFAULT '0',
  `Burgos` int(11) DEFAULT '0',
  `Cabugao` int(11) DEFAULT '0',
  `Candon` int(11) DEFAULT '0',
  `Caoayan` int(11) DEFAULT '0',
  `Cervantes` int(11) DEFAULT '0',
  `Gregorio del Pilar` int(11) DEFAULT '0',
  `Lidlidda` int(11) DEFAULT '0',
  `Magsingal` int(11) DEFAULT '0',
  `Nagbukel` int(11) DEFAULT '0',
  `Narvacan` int(11) DEFAULT '0',
  `Quirino` int(11) DEFAULT '0',
  `Salcedo` int(11) DEFAULT '0',
  `San Emilio` int(11) DEFAULT '0',
  `San Esteban` int(11) DEFAULT '0',
  `San Ildefonso` int(11) DEFAULT '0',
  `San Juan` int(11) DEFAULT '0',
  `San Vicente` int(11) DEFAULT '0',
  `Santa` int(11) DEFAULT '0',
  `Santa Catalina` int(11) DEFAULT '0',
  `Santa Cruz` int(11) DEFAULT '0',
  `Santa Lucia` int(11) DEFAULT '0',
  `Santa Maria` int(11) DEFAULT '0',
  `Santiago` int(11) DEFAULT '0',
  `Santo Domingo` int(11) DEFAULT '0',
  `Sigay` int(11) DEFAULT '0',
  `Sinait` int(11) DEFAULT '0',
  `Sugpon` int(11) DEFAULT '0',
  `Suyo` int(11) DEFAULT '0',
  `Tagudin` int(11) DEFAULT '0',
  `Vigan` int(11) DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `puimunicipalities` (`ID`, `ReportDate`, `Alilem`, `Banayoyo`, `Bantay`, `Burgos`, `Cabugao`, `Candon`, `Caoayan`, `Cervantes`, `Gregorio del Pilar`, `Lidlidda`, `Magsingal`, `Nagbukel`, `Narvacan`, `Quirino`, `Salcedo`, `San Emilio`, `San Esteban`, `San Ildefonso`, `San Juan`, `San Vicente`, `Santa`, `Santa Catalina`, `Santa Cruz`, `Santa Lucia`, `Santa Maria`, `Santiago`, `Santo Domingo`, `Sigay`, `Sinait`, `Sugpon`, `Suyo`, `Tagudin`, `Vigan`) VALUES
(1, '2020-04-05 17:00:00', 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 1, 1, 1, 2, 0, 0, 0, 0, 0, 0, 1, 1, 1, 0, 0, 0, 0, 1, 0, 4, 3),
(3, '2020-04-06 10:00:00', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2, 0, 1, 1, 0, 2, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 1, 0, 4, 3),
(4, '2020-04-06 17:00:00', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 1, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 1, 0, 4, 2),
(5, '2020-04-07 10:00:00', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 1, 1, 3, 1, 1, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 1, 0, 5, 2);

[dee-u]

This appears to do it, marking thread as Resolved but just in case someone else has other ideas then please share.

SET @sqlVigan = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(case when s.ReportDate = ''',
      dt,
      ''' then s.Vigan else 0 end) AS `',
      DATE_FORMAT(dt, '%M %d, %Y %h:%i %p') , '`'
    )
  ORDER BY dt ASC) INTO @sqlVigan
FROM
(
  SELECT s.ReportDate AS dt
  FROM puimunicipalities s
  ORDER BY dt ASC
) d;

SET @sqlVigan 
  = CONCAT('SELECT ''Vigan'' as Municipality, ', @sqlVigan, ' 
            from puimunicipalities s');
            
            
SET @sqlTagudin = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(case when s.ReportDate = ''',
      dt,
      ''' then s.Tagudin else 0 end) AS `',
      DATE_FORMAT(dt, '%M %d, %Y %h:%i %p') , '`'
    )
  ORDER BY dt ASC) INTO @sqlTagudin
FROM
(
  SELECT s.ReportDate AS dt
  FROM puimunicipalities s
  ORDER BY dt ASC
) d;

SET @sqlTagudin 
  = CONCAT('SELECT ''Tagudin'' as Municipality, ', @sqlTagudin, ' 
            from puimunicipalities s');


SET @sql = CONCAT(@sqlVigan,' UNION ALL ',@sqlTagudin);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;