CODEWITHSUNDEEP
mysqlsql
Greg B
Greg B

Reputation: 619

How to group rows into output columns, by date

I have data in a MariaDB table similar to the following. It is basically weather data at two locations, and I want to feed the data to a stats program. I want to output the data in a way that groups the rows by datetime, but puts the grouped row values into columns.

obsv_location   obsv_value    obsv_datetime
-------------   ----------    -------------
airport1        35.0          2020-01-01 12:00
airport2        35.2          2020-01-01 12:00
airport1        36.5          2020-01-01 13:00
airport2        36.4          2020-01-01 13:00

Is it possible to create a query that outputs something like the following?

obsv_datetime     airport1    airport2
-------------     --------    -------------
2020-01-01 12:00  35.0        35.2
2020-01-01 13:00  36.5        36.4

Upvotes: 1

Views: 27

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271161

One method uses join; another conditional aggregation. The second:

select obsv_datetime,
       max(case when obsv_location = 'airport1' then obsv_value end) as airport1,
       max(case when obsv_location = 'airport2' then obsv_value end) as airport2
from t
group by obsv_datetime;

Upvotes: 1

Related Questions