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