MySQL Forums
Forum List  »  Microsoft Access

Re: Query transform / pivot
Posted by: Peter Brawley
Date: May 03, 2020 04:14PM

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 |
+--------+------------+------------+

Options: ReplyQuote


Subject
Views
Written By
Posted
185
May 03, 2020 12:15AM
Re: Query transform / pivot
101
May 03, 2020 04:14PM
102
May 03, 2020 05:49PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.