Transpose row values to columns

Question

My query is simple - it grabs banks and their transaction sums per month/year:

select bank_name, 
   sum(payment_sum), 
   CONCAT(MONTH(payment_due_date), '/', YEAR(payment_due_date)) as month_year
from bank
join finance 
  on bank.bank_id = finance.bank_id
group by bank_name, month_year

Returns:

enter image description here

How do I transponse row values (month_year) into columns, followed by payment_sum also transponded to corresponding month_year generated columns?

I'm looking for a way to find a sum of payments for each bank by month.

I looked at this topic but my needs seem different.

score 2 · Accepted Answer · answered Sep 11 '13 at 14:31

Unfortunately MySQL doesn't have a PIVOT function which will convert the rows into columns, so you will need to use an aggregate function with a CASE expression.

The basic syntax of the query will be if you have a limited or known number of columns:

select bank_name,
  sum(case when month_year ='10/2011' then payment_sum else 0 end) `10/2011`,
  sum(case when month_year ='11/2011' then payment_sum else 0 end) `11/2011`,
  sum(case when month_year ='12/2011' then payment_sum else 0 end) `12/2011`
from
(
  select bank_name, 
     payment_sum, 
     CONCAT(MONTH(payment_due_date), '/', YEAR(payment_due_date)) as month_year
  from bank
  join finance 
    on bank.bank_id = finance.bank_id
) d
group by bank_name;

But for your situation, it sounds like you will have an unknown number of dates, so you might need to use a prepared statement to create dynamic sql:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(case when month_year = ''',
      CONCAT(MONTH(payment_due_date), '/', YEAR(payment_due_date)),
      ''' then payment_sum end) AS `',
      CONCAT(MONTH(payment_due_date), '/', YEAR(payment_due_date)), '`'
    )
  ) INTO @sql
from finance;

SET @sql 
    = CONCAT('SELECT bank_name, ', @sql, ' 
              from
              (
                select bank_name, 
                   payment_sum, 
                   CONCAT(MONTH(payment_due_date), ''/'', YEAR(payment_due_date)) as month_year
                from bank
                join finance 
                  on bank.bank_id = finance.bank_id
              ) d
              group by bank_name;');

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

Transpose row values to columns

1 Answers1