-1

Got a table in mysql with the below columns and rows

Table1
-------------------------------------------------------------
date_time           Place         Cat 1 Cat 2   Cat 3
-------------------------------------------------------------
5/14/2019 3:54           FUJ                     Yes
5/14/2019 4:19           FUJ                Yes      Yes
5/15/2019 1:22           FUJ                     Yes
5/14/2019 11:08          MUM            Yes Yes 
5/15/2019  16:34:00 PM   MUM                     Yes

Need help in getting a pivot table as below desired output

Places  Categories  5/14/2019   5/15/2019
-------------------------------------------------------------
FUJ Cat 1               0               0
    Cat 2               1               0
    Cat 3               2               1
MUM Cat 1               1               0
    Cat 2               1               0
    Cat 3               0               1

Thank you in advance...

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Rajesh Iyer
  • 1
  • 3
  • 2
    Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) – Dharman May 29 '19 at 09:11

1 Answers1

0

A better schema might look as follows:

-----------------------------
date_time           Place Cat
-----------------------------
2019-05-14 03:54:00 FUJ     3
2019-05-14 04:19:00 FUJ     1
2019-05-14 04:19:00 FUJ     3
2019-05-15 01:22:00 FUJ     2
2019-05-14 11:08:00 MUM     1
2019-05-14 11:08:00 MUM     2
2019-05-15 16:34:00 MUM     3

This isn't an answer, but it is a better place to start; the rest of the problem would ordinarily be solved in your application code/presentation layer (i.e. php)

Strawberry
  • 33,750
  • 13
  • 40
  • 57
  • i wanted to download the same report date wise, and category wise and place wise – Rajesh Iyer May 29 '19 at 10:16
  • Yep, that would just be `SELECT * FROM the_above ORDER BY place, category, date_time`. Everything else would typically be handled in your application code. – Strawberry May 29 '19 at 11:08