MySql Pivotal table query group by multiple columns

Question

The below MySQL table looking something like this below.

consulted_on (DATE)   consulted_by (VARCHAR)
---------------------------------------------
04/12/2018               Mr.Bob
04/12/2018               Mr.Jhon
04/12/2018               Mr.Bob
05/12/2018               Mr.Jhon
06/12/2018               Mr.Bob
06/12/2018               Mr.Jhon
07/12/2018               Mr.Bob

I would like to display the data as below , Is it possible to get the output like below ?

consulted_on(DATE)  Mr.Bob   Mr.Jhon  
-------------------------------------
04/12/2018           2              1
05/12/2018           0              1
06/12/2018           1              1
07/12/2018           1              0

The consulted_by column is basically contains dynamic value, in this case how do I write pivot query in mysql ? can any one help me with this ?

Many Thanks

Vikrant Naik · Accepted Answer · 2018-12-04T09:51:43.550

1

Please use Below query:

SELECT
  consulted_on,  
  SUM(CASE WHEN (consulted_by='Mr.Bob') THEN 1 ELSE 0 END) AS Mr_Bob,
  SUM(CASE WHEN (consulted_by='Mr.Jhon') THEN 1 ELSE 0 END) AS Mr_Jhon,
FROM table_name
GROUP BY consulted_on

edited Dec 04 '18 at 09:51

answered Dec 04 '18 at 09:47

Vikrant Naik

36
4

Thanks for your swift reply, but the column consulted_by is the dynamic one – isudarsan Dec 04 '18 at 09:49

MySql Pivotal table query group by multiple columns

1 Answers1