Mysql – How to pivot to summarize the entries by a specific area

MySQLpivot

I have a map where every row represents a bird and a row with the attributes shown in the excerpt of the attribute table:

round     | area | date         | species     | behaviour
---------------------------------------------------------
1         | 20   | 2018-04-13   | woodpecker  | NULL
1         | 55   | 2018-04-14   | woodpecker  | NULL
1         | 21   | 2018-06-01   | robin       | NULL
1         | 55   | 2018-04-14   | blackbird   | NULL
1         | 55   | 2018-04-14   | blackbird   | NULL
1         | 55   | 2018-04-14   | robin       | NULL
2         | 55   | 2018-05-28   | chaffinch   | NULL
2         | 20   | 2018-05-29   | chaffinch   | NULL
3         | 21   | 2018-06-01   | robin       | breedig
3         | 55   | 2018-06-01   | blackbird   | NULL

Every study area was visited n-times (see column "round", where "round" is global. That means, that when we did not
find a single bird on a specific round, we have no entry for this area on this date but the round will increase next time the area is visited)

What I need is a table like that one below. I have no idea, how to get this done with SQL. What I need is something like:

*Start with lowest round
    get area and count the species for this area
    next area
next round*

The wanted table:

area  | round | date        | woodpecker | robin | blackbird | chaffinch | sum
--------------------------------------------------------------------------------
20    | 1     | 2018-04-13  | 1          | 0     | 0         | 0         | 1
55    | 1     | 2018-04-14  | 1          | 1     | 2         | 0         | 4
21    | 1     | 2018-04-01  | 0          | 1     | 0         | 0         | 1
20    | 2     | 2018-05-29  | 0          | 0     | 0         | 1         | 1
55    | 2     | 2018-05-28  | 0          | 0     | 0         | 1         | 1
21    | 3     | 2018-05-28  | 0          | 1     | 0         | 0         | 1
55    | 3     | 2018-06-01  | 0          | 0     | 1         | 0         | 1

Best Answer

Do you know that you only have woodpecker, robin, blackbird, chaffinch? To pivot at all you must either

  • Constraint the value-set in the table that you're pivoting on. Because values become schema, and schema must be determined at the time you issue the query you would have to know all possible values.
  • Make two trips to the server, one to generate the original resulset of values. And a second to read the metadata from that resultset so you can issue a second query against it.