[RESOLVED] MySQL: Date rows as columns

[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;