Re: Query transform / pivot

[Peter Brawley]

MySQL doesn't have PIVOT syntax. Has to be assembled using basic sql ops.

Two stages in the logic ...

1 build the list of possible pivoting values,

2 work that list into a query that outputs their sums

Your didn't provide your DDL or sample data, so I built a toy table from your SQL Server code ...

drop table if exists remission;
create table remission (client varchar(64), datepay date, total decimal(8,2) );
insert into remission values
('a','2020-1-1',1000),('a','2020-1-15',1000),
('b','2020-1-1',1100),('b','2020-1-15',1100),
('c','2020-1-1',1200),('c','2020-1-15',1200),
('d','2020-1-1',1300),('d','2020-1-15',1300);

Here's the simplest way I know to pivot datepay on client summing amount in MySQL ...

-- 1 assemble pivot expressions
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(if(datepay = ''', datepay,
      ''', total, 0)) AS `', datepay ,'`'
    )
  ) 
FROM remission INTO @sql;

-- 2 assemble query around the above result
SET @sql2 = 
  CONCAT( 'SELECT t.client, ', @sql, ' FROM (
                  select client, datepay, total
                  from remission
        )t
group by t.client
order by t.client');
PREPARE stmt FROM @sql2;
EXECUTE stmt;
DROP PREPARE stmt;
+--------+------------+------------+
| client | 2020-01-01 | 2020-01-15 |
+--------+------------+------------+
| a      |    1000.00 |    2000.00 |
| b      |    1100.00 |    2100.00 |
| c      |    1200.00 |    2200.00 |
| d      |    1300.00 |    2300.00 |
+--------+------------+------------+