2

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
2
  • MySQL do not know about PIVOT. If species values list is absolutely (and forever) static, you can create complex SQL text using construction near SUM(species='woodpecker'). Else - use stored procedure which will pivot your data.
    – Akina
    Nov 23, 2018 at 19:46
  • This will build and execute the code for pivoting.
    – Rick James
    Nov 23, 2018 at 20:45

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.