Pivot MySQL Table with Dynamic Columns

Question

I posted on an issue I was having before in representating data in a certain way and after 2 posts I was able to get enough information to realise what I need is a pivot table with dynamic columns.

I have been able to create a static pivot table with great effect but for serious reporting they are useless. Here is what I have done so far:

select `title` as `Payment Method`,
  sum(case when MONTH(t1.`month_payment`) = 1 then amount else 0 end) Jan,
  sum(case when MONTH(t1.`month_payment`) = 2 then amount else 0 end) Feb,
  sum(case when MONTH(t1.`month_payment`) = 3 then amount else 0 end) Mar,
  sum(case when MONTH(t1.`month_payment`) = 4 then amount else 0 end) Apr,
  sum(case when MONTH(t1.`month_payment`) = 5 then amount else 0 end) May,
  sum(case when MONTH(t1.`month_payment`) = 6 then amount else 0 end) Jun,
  sum(case when MONTH(t1.`month_payment`) = 7 then amount else 0 end) Jul,
  sum(case when MONTH(t1.`month_payment`) = 8 then amount else 0 end) Aug,
  sum(case when MONTH(t1.`month_payment`) = 9 then amount else 0 end) Sep,
  sum(case when MONTH(t1.`month_payment`) = 10 then amount else 0 end) Oct,
  sum(case when MONTH(t1.`month_payment`) = 11 then amount else 0 end) Nov,
  sum(case when MONTH(t1.`month_payment`) = 12 then amount else 0 end) `Dec`

from record_payment t1
join setting_payment_method ON
setting_payment_method.id = t1.method_id
where `month_payment` >= '$date_from'
  and `month_payment` <= '$date_to'
group by title with rollup

this is able to get me a static report from January to December but the issue is if the user searches from January 2015 to December 2017, the table will sum and aggregate Jan 2015, 2016 and 2017 all into the January that is displayed to the user which is wrong.

I have also produced a static pivot for yearly report:

select `title` as `Payment Method`,
  sum(case when YEAR(t1.`month_payment`) = 2013 then amount else 0 end) `2013`,
  sum(case when YEAR(t1.`month_payment`) = 2014 then amount else 0 end) `2014`,
  sum(case when YEAR(t1.`month_payment`) = 2015 then amount else 0 end) `2015`,
  sum(case when YEAR(t1.`month_payment`) = 2016 then amount else 0 end) `2016`,
  sum(case when YEAR(t1.`month_payment`) = 2017 then amount else 0 end) `2017`

from record_payment t1
join setting_payment_method ON
setting_payment_method.id = t1.method_id
where `month_payment` >= '$date_from'
  and `month_payment` <= '$date_to'
group by title with rollup

but sometimes you need reports that give you the breakdown of the year in months over a long period (e.g 12+) so you can see a trend.

My attempt to do an automatic pivot was not so successful:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(case when month_payment = ''',
      month_payment,
      ''' then amount end) AS `',
      month_payment,
      '`'
    )
  ) INTO @sql
FROM  record_payment;

SET @sql = CONCAT('SELECT `title` as `Payment Method`, ', @sql, ' FROM record_payment t1
join setting_payment_method ON
setting_payment_method.id = t1.method_id
where `month_payment` >= '$date_from'
  and `month_payment` <= '$date_to'
group by title with rollup');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Any help is appreciated.

Are you trying to do the whole thing in one call to mysqli_query()? You can't do multiple statements with one call, you need a separate call for each statement. — Barmar
– Barmar, Commented Sep 5, 2017 at 19:31
You need the same WHERE condition in your first query. But I would just group by title, year and format the result in PHP. — Paul Spiegel
– Paul Spiegel, Commented Sep 5, 2017 at 19:43

Bill Karwin · Accepted Answer · 2017-09-07 20:55:53Z

2

I guess your month_payment column is a DATE (btw, should post the SHOW CREATE TABLE when you ask SQL questions so we don't have to guess).

But your first query is not formatting that to the year/month. Nor is it limiting the date range.

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN EXTRACT(YEAR_MONTH FROM month_payment) = ',
      EXTRACT(YEAR_MONTH FROM month_payment),
      ' THEN AMOUNT END) AS `',
      EXTRACT(YEAR_MONTH FROM month_payment),
      '`'
    )
  ) INTO @sql
FROM record_payment
WHERE month_payment BETWEEN ? AND ?

See https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_extract

Despite Barmar's suggestion, there's no reason to put this in a stored procedure. You tagged your question with PHP, and you can do this in PHP just fine:

<?php

...get a PDO connection...

$sql = "
    SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'SUM(CASE WHEN EXTRACT(YEAR_MONTH FROM month_payment) = ',
          EXTRACT(YEAR_MONTH FROM month_payment),
          ' THEN AMOUNT END) AS `',
          EXTRACT(YEAR_MONTH FROM month_payment),
          '`'
        )
      ) AS `pivot_columns`
    FROM record_payment
    WHERE month_payment BETWEEN ? AND ?
";
$stmt = $pdo->prepare($sql);
$date_from = '2017-01-01';
$date_to   = '2017-08-01';
$stmt->execute([$date_from, $date_to]);
$row = $stmt->fetch();
$stmt->closeCursor();
$pivot_columns = $row['pivot_columns'];

$sql = "
    SELECT title AS `Payment Method`, {$pivot_columns}
    FROM record_payment t1
    JOIN setting_payment_method spm ON spm.id = t1.method_id
    WHERE month_payment BETWEEN ? AND ?
    GROUP BY title WITH ROLLUP
";
echo $sql;
$stmt = $pdo->prepare($sql);
$stmt->execute([$date_from, $date_to]);
$results = $stmt->fetchAll();
$stmt->closeCursor();

print_r($results);

edited Sep 7, 2017 at 20:55

answered Sep 5, 2017 at 20:06

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Enoch Over a year ago

Yes, the month_payment is a date datatype. * Thanks for that tip. so in my stored procedure my IN parameters are my 2 dates but i'm a little confused on executing the statement. At which stage do I call the stored procedure? USUALLY you can call the procedure like this ... SET @p0='2015-01-01'; SET @p1='2017-01-01'; CALL ` payment_summary ` (@p0, @p1);

Bill Karwin Over a year ago

@EnochS see the PHP example I added to my answer above.

Enoch Over a year ago

I've tested it but the array keeps returning empty

Bill Karwin Over a year ago

@EnochS I have updated my code above. I found that using fetchColumn() didn't work how I expected it to. I pasted the code literally from what I tested in my environment.

Enoch Over a year ago

Thanks Bill it's working, I just need to do some manipulation on the interface. Marked as correct answer!

Collectives™ on Stack Overflow

Pivot MySQL Table with Dynamic Columns

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related