Pivot table in Bigquery

Question

I have the following query:

SELECT DISTINCT
    Mes,
    cliente,
    Squad,
    SUM(Trafego_Estimado) AS Trafego_Estimado
FROM `Ranktank.RT_BD`
    GROUP BY Mes, cliente, Squad;

that returns me: *traffic varies on each line

Mes,     client,    Squad,  Trafic  
2020-11, A,         UM,     15664.47
2020-10, B,         DOIS,   15664.47
2020-09, C,         TRES,   15664.47
2020-19, A,         UM,     15664.47
2020-11, B,         DOIS,   15664.47
2020-10, C,         TRES,   15664.47
2020-10, A,         UM,     15664.47
2020-09, B,         DOIS,   15664.47
2020-11, C,         TRES,   15664.47

I'm trying to streamline the spreadsheet with this structure like this:

Cliente, Squad, 2020-11,  2020-10,  2020-09
A,       UM,    15664.47, 15664.47, 15664.47
B,       DOIS,  15664.47, 15664.47, 15664.47
C,       TRES,  15664.47, 15664.47, 15664.47

Is it possible to do that?

I tried to do it this way, but it didn't work: How to Pivot table in BigQuery

Alessandro · Answer 1 · 2021-03-20T05:36:16.307

I was able to pivot your table with Hoffa solution (with some adjustments). The issue is the values in your table for the column "mes". You cannot have column names with "-" with this method, because they need to be quoted. What you can do is to replace the "-" with "_".

SELECT DISTINCT
    replace(Mes, '-', '_') as Mes,
    cliente,
    Squad,
    SUM(Trafego_Estimado) AS Trafego_Estimado
FROM `Ranktank.RT_BD`
GROUP BY Mes, cliente, Squad;

Then you have to save the result in a table because the pivot script needs a table in input. Now, you can run the pivot procedure. Remember that the procedure is in the US region, so or you copy the code and the normalisation function in your dataset or your data need to be in US too. Then you can run:

call `fhoffa.x.pivot`(
    'project_id.dataset_id.data_to_pivot'
    , 'project_id.dataset_id.data_pivotted'
    , ['Client', 'Squad']
    , 'Mes'
    , 'Trafego_Estimado'
    , 5
    , 'any_value'
    , '');

Pivot table in Bigquery

1 Answers1