Convert column oriented data to line oriented data

Question

I have data under this schema in my table (AVG_TEAM field is AVG(AVG_OK) where TEAM_ID = TEAM_ID in line and MONTH = MONTH in line)

DRIVER_ID  | MONTH  | TEAM_ID  | AVG_OK  |  AVG_TEAM
----------------------------------------------------
     005     201901     XXXX      81          84
     005     201902     XXXX      84          82
     005     201903     XXXX      81          80
     005     201904     ZZZZ      84          75
     070     201901     RRRR      77          80
     070     201902     RRRR      80          80

etc    

Some people would like to follow each driver's evolution throughout the monthes. Expected schema would be :

DRIVER_ID | TEAM_ID  | AVG_OK_MONTH1  |  AVG_OK_MONTH2  |  AVG_OK_MONTH3  |   ...  |  AVG_OK_MONTH12  | GLOBAL_AVG  |  TEAM_GLOBAL_AVG
    005       XXXX          81                84               81             ...          NULL               82            ?
    070       RRRR          77                80               NULL           ...          NULL              78.5           ?
etc

As you very likely saw, there is already a big flaw in this approach as the TEAM_ID might change with the month, and therefore the AVG_TEAM field cannot be used to easily compute the TEAM_GLOBAL_AVG field

But let's say we put this flaw away, and consider the TEAM_ID wont change. I cannot see an easy approach to convert the first schema to the other, weither with SQL or with PHP (never done that).

I have some very ugly solutions in mind using arrays in PHP but there has got to be better and simpler ways ? Any insight is welcomed.

Answer 1

I think you can use conditional aggregation. Assuming that month is a date:

select driver_id,
       max(case when month(month) = 1 then avg_ok end) as avg_ok_1,
       max(case when month(month) = 2 then avg_ok end) as avg_ok_2,
       max(case when month(month) = 3 then avg_ok end) as avg_ok_3,
       max(case when month(month) = 4 then avg_ok end) as avg_ok_4
from t
where month >= '2019-01-01' and month < '2020-01-01'
group by driver_id;

Otherwise, you can do something similar with string or date functions:

select driver_id,
       max(case when right(month, 2) = '01' then avg_ok end) as avg_ok_1,
       max(case when right(month, 2) = '02' then avg_ok end) as avg_ok_2,
       max(case when right(month, 2) = '03' then avg_ok end) as avg_ok_3,
       max(case when right(month, 2) = '04' then avg_ok end) as avg_ok_4
from t
where month >= '201901' and month < '202001'
group by driver_id;