How to get dynamic year wise data in mysql pivot table

Asked Dec 03 '18 at 11:27

Active Dec 03 '18 at 11:46

Viewed 172 times

I want to get year wise data in MySQL pivot table.I have one table

id | Name  | Amount | Date    
1  | ABC   | 50     | 2018-05-16    
2  | PQR   | 60     | 2017-07-16    
3  | LMN   | 40     | 2017-01-16

I want result Like

Type  | 2018 | 2017
total |   1  |  2  
SUM   |   50 | 100

Also date column values should be dynamic. If new year date is entered,The pivot table should contain that year i.e. if date value is 2019-01-22 then pivot table will show 2019 as column

edited Dec 03 '18 at 11:46

Nick

138,499
22
57
95

asked Dec 03 '18 at 11:27

Kiran Pardeshi

In what way would the year be dynamic? Do you only want the last 2 years based on current year ,or the last 2 years based on a supplied year or something else? – P.Salmon Dec 03 '18 at 11:31
No, I want all years as per date values are present in table – Kiran Pardeshi Dec 03 '18 at 11:32
Possible duplicate of [MySQL - Rows to Columns](https://stackoverflow.com/questions/1241178/mysql-rows-to-columns) – Sonal Borkar Dec 03 '18 at 11:33
A little different to the usual mysql row to columns in that are going to have to union 2 queries 1 for the total/count row and 1 for the sum row. – P.Salmon Dec 03 '18 at 11:36
how is it possible ?? – Kiran Pardeshi Dec 03 '18 at 11:38

How to get dynamic year wise data in mysql pivot table

0 Answers0