0

I have a table:

   date       Name
2017-02-21    Mark
2017-02-21    Peter
2017-02-21    John
2017-02-22    Mark
2017-02-22    Mark
2017-02-22    Mark
2017-02-22    Peter

And I want the following output:

    date    Mark  John  Peter
2017-02-21   1      1    1
2017-02-22   3      0    1

How can I achieve this in MySQL?

Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
Mayank Singhal
  • 19
  • 2
  • 1
    This is a simple pivot, but note that issues of data display are generally best resolved in the presentation layer, assuming that's available – Strawberry Feb 26 '17 at 12:56
  • @Strawberry regardless where you want to do the pivot, this wuestion is a duplicate, see link – Shadow Feb 26 '17 at 13:37
  • The duplicate topic describes both static and dynamic pivoting in mysql and provides explanations – Shadow Feb 26 '17 at 13:42
  • @shadow, regardless, the pivoting should not in my view be done in MySQL – Strawberry Feb 26 '17 at 13:43

3 Answers3

1

You can use aggregation to pivot your data like this:

select date,
    sum(Name = 'Mark') Mark,
    sum(Name = 'John') John,
    sum(Name = 'Peter') Peter
from your_table
group by date;

It uses the fact that MySQL evaluates true to 1 and false to 0.

SQLFiddle

Community
  • 1
  • 1
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
1

use 'FIELD()'

select 
sum(field(name,'mark')) as mark,
sum(field(name,'peter')) as peter,
sum(field(name,'john')) as john 
from table_name group by date;
denny
  • 2,084
  • 2
  • 15
  • 19
0

This is not exactly what you want, but you can use GROUP BY to group results and then count names, like this:

SELECT *,
    COUNT(name) as how_many
FROM your_table
GROUP BY date, name

SQL Fiddle

Albert221
  • 5,223
  • 5
  • 25
  • 41